Text-to-columns in Numbers

If "text to columns" is referring to Excel's operation, Numbers does not have that. I believe there is a script available to do it, though. You can also do it with the new REGEX.EXTRACT formula. If your data is in column A, the formula in the cells of column B would be

=IFERROR(REGEX.EXTRACT($A,"\S+",COLUMN()−1),"")

That extracts the first "word"

Fill right with the formula to column C, D, etc to extract the 2nd, 3rd, etc. "words"

If, instead, you want to separate your data into columns of degrees, minutes, seconds then the formulas are

=IFERROR(REGEX.EXTRACT($A,"\S+°"),"")

=IFERROR(REGEX.EXTRACT($A,"\S+′"),"")

=IFERROR(REGEX.EXTRACT($A,"\S+″"),"")

Luckily your data does not use the quotation symbol for "seconds" because I don't know how to extract that. The ″ character (unicode 8243) was no problem, though.

The formula does not work for the first or last cell in your column of data that are not formatted that way

Forget the formulas. I think you are double clicking on a cell before pasting in the data. It goes into just that one cell if you do.

Try selecting a cell (one click) then paste. It spreads across the rows and columns if I do.

It's not clear whether the data imports into Numbers all in a column as shown in your first post or all in a column as in your second post. Assuming it comes in all vertically in a column as in your first post then you can do something like this:

The imported data is in column A.

Then in B1 you can put this formula, and copy it right and down.

=INDEX($A,(COLUMN()−1)+(ROW()−1)*8)

Once you've got the data across columns the way you want you can "remove the formulas" by selecting the columns, command-c to copy, then Edit > Paste Formula Results.

SG

Formula in B2 =REGEX.EXTRACT($A$1,".+(\n\r|$)",(COLUMN()−1)+8×(ROW()−1))

Fill right and fill down.

Note that my data is not all the same as yours. I entered some then copy/pasted and changed a number here and there. It was too tedious to type it all in.

Without the actual data I don't know what line feed character is being used. The formula I used assumes it is a line feed (ASCII 10) or carriage return (ASCII 13) or both. I was not able to test with anything but the line feed (it can be created using Option-Return when entering data into a cell). If it does not work with your data, it will probably require only a minor tweak.

Which support materials? Where are you getting the data? How are you importing it?

Is what you show in one cell in Numbers? Or is it in multiple cells one below the other? Where are the dates that go with the data?

SG

If that is not all in one cell but rather each line in a separate cell (you show know row numbers or column letters in your screenshot so it is impossible to tel) then I suggest giving my suggested formula a try.

If it is all in one cell then you may need a script or another solution.

Perhaps you could give the name of the website where you are getting the data.

SG

The setting you can't see does not seem to be of any importance to the task at hand.

You will need one column to hold the original value, and three columns to hold the three parts of each vslue.

One difficulty will be the lack of a consistent format for all of the listed values.

Most are degrees, minutes and seconds, with the signs °, ', and " to identify the unit for each part of the value.

Exceptions are the first value in the list: 01.01, whose format suggests it may be a measure of an angle expressed in radians, rather than in degrees, minutes and seconds,

The penultimate value, which uses the most common notation, but is missing a value for degrees,

And the last: 147 093 406.74, for which I see no indication of how it fits into the collection.

For those in the 'standard' notation, the formulas in columns B, C and D, written as they would appear in row 2 of the table, would be as shown below the table:

In order from the top, the formulas are those for cells B2, C2 and D2.

For the list, each would be filled down its column for as many cells as needed.

The non-conforming values mentioned above would need to be converted to this standard notation for these formulas to be useful with those examples.

Regards,

Barry

01.01

−69° 54′ 51″

+69° 44′ 47″

+26° 20′ 48″

−22° 56′ 28″

+282° 30′ 16″

32′ 32″

147 093 406.74