Put string of comma-delimited numbers in successive rows of a single column.
E.g.: a set of certain frame numbers from a roll of film
[Re-Titled by Moderator]
MacBook Pro 15″, macOS 13.7
And if the import is still not working even with multiple rows then you can try this if you don't have TEXTSPLIT in your version of Numbers.
In B2, filled right:
=REGEX.EXTRACT($A2, "[^,]+", COLUMN()−1, 0)
More on that function here:
SG
And if the import is still not working even with multiple rows then you can try this if you don't have TEXTSPLIT in your version of Numbers.
In B2, filled right:
=REGEX.EXTRACT($A2, "[^,]+", COLUMN()−1, 0)
More on that function here:
SG
What happens when you import "many rows" of your data? Does it spread across the columns like comma-separated data is supposed to do, with one set of data per row? If so, you can transpose the table after you import the data to make it go the other direction. Or does it paste each one into column A, like the single one you took a screenshot of? Or does it paste all of it into one cell?
If it imports into a single column, try this:
Numbers seems to understand what to do with a CSV file, even if the number of commas is different per row.
Use a custom delimiter that is not present in your data. For instance "~", or any special character you are certain is not in your data.
For more information see section "Change setting for a delimited text file" in this article: Import an Excel or text file into Numbers on Mac - Apple Support.
Dear BU: I've tried using the Insert /Choose menu to bring in my short list of numbers, either in the form of a .pdf or a copy/paste from the original text file, as you imply or mention.
Either way, no popup comes up, nor do any of the four formatting options offer a solution.
e.g.:
As mentioned originally, we want one of these numbers in each consecutive cell of a single column.
Thanks!
El Mero wrote:
we want one of these numbers in each consecutive cell of a single column.
If you can't get Numbers to split them when you import then can try using formula to split them after the import.
You could try TEXTSPLIT, like this:
In B2 of the example:
=TEXTSPLIT(A2,",")
I could tell if you had spaces between the values but if you don't get the result you want you could try ", " instead of ",".
Note that the formula will complain if you don't have enough columns in the table. The error will go away if you simply add more columns.
SG
Upgrade to 14.4 to get TEXTSPLIT?
I thought your problem was different. You might try pasting two or more of them into TEXTEDIT, Copy, then Paste into numbers.
After importing/pasting, you'll get a popup about "table data was imported and can be adjusted". Click on that. Assuming no tabs are in your data, click on the tab to select it as a delimiter (it will highlight it green) and click on the comma to unhighlight it. Or type a custom delimiter and unhighlight the comma.
If Numbers doesn't see your import/paste as tabular data, which can happen if there are more "columns" in some rows than in others in the data, it will paste each row into a single column without having to do any of the above steps. This does not seem to be the case for you but, if you see that happen, this is why.
Do you have at least two rows, or even better three rows in your data, each with the same number of values and comma delimiters? You need multiple rows for Numbers to recognize it as tabular data.
If not, try duplicating the one row you have so you have two or there, opening in Numbers, and then deleting the unneeded row.
SG
Since the answers are not working for you I am assuming I am misunderstanding the problem. Let me ask, just to be clear, what it is you want. You posted a screenshot of a cell (cell A1) with comma-delimited values in it. You want those values to be separated down column A so that A1 has "Roll # 123 Red", A2 has "0", A3 has "1", etc.? That's not what I thought you wanted but now I am wondering if it is. Is that what you want? Will you have more than just this one set of data? If you have two or more, will the other ones be in column B, C, and so on? Is that were they end up now when you import the data?
The answer to your first question, above, is "yes," Badunit.
As to # of sets of data, I have many rows with similar but different frame# lists, some with fewer counts of numbers. (This speaks more to SGIII's warning about tabular data...)
I'm gonna study REGEX.EXTRACT - Apple Support and see if I can apply it...
Thanks, BU, but my inputs are coming from the TextEdit app.
I've tried both .RTF & as .pdf.
This 2017 "Butterfly" says all updates are installed, i.e. Num. 14.1 seems to be the ultimate available (?).
Thanks, SG, that looks like a useful formula-- I'll try it.
There's probably a way to get that long row converted to a single column, right?
Turns out, Numbers 14.1 doesn't seem to have Textsplit, only TextAfter, etc.
How to import comma-delimited numbers into a single column in Numbers 14.1?
