How to "text to column" (feature of Excel) in Numbers

Hi msstatz,


Welcome to Apple Discussions and the Numbers '09 forum.

msstatz wrote:
Column A has 20 rows. Each cell in column A has data that needs to be separated into separate columns.
Imagine data with the following labels; first name, middle initial, last name street address, city, state, zip, phone and email after the procedure.
Some names do not have a middle initial.
Street addresses have multiple formats - 9 Wakeman St., 210 E Lake St., 414 Tall Pines Rd, 88 Main St Apt #7.
Cities are followed by a comma.
Some zips have the +4 format.
Some phone numbers have area code.

Is there a formula that would look like =A(content until the first space) or =A(content until the next comma)?

FIND(" ",A) will return the position of the first occurrence of a space in the string in A.
=TRIM(LEFT(A,FIND(" ",A))) will return the content until the first space, and remove the trailing space.

=TRIM(RIGHT(A,LEN(A)-FIND(",",A)-1)) will return everything after the first occurrence of a comma and remove the leading space.

Beyond that, it gets a bit complicated. 😉
No time to go into it further at the moment, and I suspect there may be a simpler solution using AppleScript than possible using formulas.

Back later with more. Meantime, the following information might clarify the problem:

How are the individual elements of the data separated? (the elements may be the items you've identified in the list above, or the first element could be the full name. From your description it would appear that two typical entries in column A might look like this:

John Q. Public 123 Any ST Big City, AK 12345-1234 222-2222 jqp@public.com
John Public 123 Any Street Big City, Alaska 12345 (222) 222 2222 jqp@public.com

I just converted from Excel to Numbers and am not convinced it was a good idea?
Not a macro user but could probably figure out an entry level macro example and expand on it.

Use the tool that fits the job. Numbers doesn't support macros, but can be scripted using AppleScript. Yvan is the expert there.

Regards,
Barry

Here is a script which may help.

--

--[SCRIPT]
(*
I'm too tired to build a full script.
Here you have just an example of the way to use the needed handler.
When the script has finished, paste where you whish.
Paste and Apply Style is the best choice.
Yvan KOENIG (VALLAURIS, France)
2010/09/24
*)
--=====
on run
 set fichesentexte to "John Q. Public 123 Any ST Big City, AK 12345-1234 222-2222 jqp@public.com
John Public 123 Any Street Big City, Alaska 12345 (222) 222 2222 jqp@public.com"
 
 set fichesenliste to paragraphs of fichesentexte
 repeat with une_reference in fichesenliste
 set contents of une_reference to my analyse(une_reference as text)
 end repeat
 
 set the clipboard to my recolle(fichesenliste, return)
 
end run
--=====
on analyse(une_chaine)
 local liste_courte, une_reference, un_caractere, un_extrait
 local liste_longue, position_virgule, position_espace, debut_suite
 (*
Extract the name *)
 set liste_courte to {}
 repeat with une_reference in une_chaine
 set un_caractere to une_reference as text
 if un_caractere is in {"1", "2", "3", "4", "5", "6", "7", "8", "9", "0"} then
 exit repeat
 else
 copy un_caractere to end of liste_courte
 end if
 end repeat
 set un_extrait to liste_courte as text
 set debut_suite to 1 + (count of un_extrait)
 repeat while un_extrait ends with space
 set un_extrait to text 1 thru -2 of un_extrait
 end repeat
 set liste_longue to {un_extrait}
 set une_chaine to text debut_suite thru -1 of une_chaine
 repeat while une_chaine starts with space
 set une_chaine to text 2 thru -1 of une_chaine
 end repeat
 (*
Extract the street component *)
 set position_virgule to offset of "," in une_chaine
 set un_extrait to text 1 thru (position_virgule - 1) of une_chaine
 copy un_extrait to end of liste_longue
 set une_chaine to text (position_virgule + 1) thru -1 of une_chaine
 repeat while une_chaine starts with space
 set une_chaine to text 2 thru -1 of une_chaine
 end repeat
 (*
Extract the city *)
 set position_espace to offset of space in une_chaine
 set un_extrait to text 1 thru (position_espace - 1) of une_chaine
 copy un_extrait to end of liste_longue
 set une_chaine to text (position_espace + 1) thru -1 of une_chaine
 repeat while une_chaine starts with space
 set une_chaine to text 2 thru -1 of une_chaine
 end repeat
 (*
Extract the zip code *)
 set liste_courte to {}
 repeat with une_reference in une_chaine
 set un_caractere to une_reference as text
 if un_caractere is in {"1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "-"} then
 copy un_caractere to end of liste_courte
 else
 exit repeat
 end if
 end repeat
 set un_extrait to liste_courte as text
 set debut_suite to 1 + (count of un_extrait)
 copy un_extrait to end of liste_longue
 set une_chaine to text debut_suite thru -1 of une_chaine
 repeat while une_chaine starts with space
 set une_chaine to text 2 thru -1 of une_chaine
 end repeat
 (*
Extract the phone number *)
 set liste_courte to {}
 repeat with une_reference in une_chaine
 set un_caractere to une_reference as text
 if un_caractere is in {"1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "-", "(", ")", space} then
 copy un_caractere to end of liste_courte
 else
 exit repeat
 end if
 end repeat
 set un_extrait to liste_courte as text
 set debut_suite to 1 + (count of un_extrait)
 repeat while un_extrait ends with space
 set un_extrait to text 1 thru -2 of un_extrait
 end repeat
 copy un_extrait to end of liste_longue
 set une_chaine to text debut_suite thru -1 of une_chaine
 repeat while une_chaine starts with space
 set une_chaine to text 2 thru -1 of une_chaine
 end repeat
 (*
Extract the mail address *)
 copy une_chaine to end of liste_longue
 (*
Builds the TAB separated value record *)
 return my recolle(liste_longue, tab)
end analyse
--=====
on recolle(l, d)
 local oTIDs, t
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d
 set t to l as text
 set AppleScript's text item delimiters to oTIDs
 return t
end recolle
--=====
--[/SCRIPT]
--

Yvan KOENIG (VALLAURIS, France) vendredi 24 septembre 2010 22:40:22

Beyond that, it gets a bit complicated.

Actually, it gets pretty simple, provided there's a consistent marker to separate the data items. Unfortunately, that appears not to be the case here.

Here's a method using the spaces in the string as a separator, and placing the elements into several separate columns. While I don't think it solves the problem, it may provide a lead to a solution.



Table 1 is a single table column containing the original data strings.

Table 2 is an intermediate table containing the locations of the markers (in this case, spaces). Column A is a header column containing 1 in every body row. The rest of the body cells contain the formula below, entered in B2 and filled right and down from there.

=FIND(" ",Table 1::$A,A+1)

The error messages in the rightmost columns are Couldn't find the value " ", then There is an error in cell..., both expected, and serving as a check that the last space in the string has been reached. (Just noticed that the final item—the email address—is missing. This revision of the formula retrieves the last item.

=IFERROR(FIND(" ",Table 1::$A,A+1),LEN(Table 1::$A))



Table 3 contains the final result. A is NOT a header row on this table. The formula below in entered in A2, then filled right and down to fill the table.

=IFERROR(TRIM(MID(Table 1::$A,Table 2::A,Table 2::B-Table 2::A)),"")

Regards,
Barry

Thanks for your contribution to this topic. A task I typically need to do is to separate a column with three names(Peter John Anthony) separated by spaces into three columns each with one name. I'm not sure how to progress beyond your earlier suggestion.

Peter Evans7 wrote:
Thanks for your contribution to this topic. A task I typically need to do is to separate a column with three names(Peter John Anthony) separated by spaces into three columns each with one name. I'm not sure how to progress beyond your earlier suggestion.

Hi Peter,

You're welcome.

I'm not sure what progress you need beyond what's above. Can you clarify?

Regards,
Barry

Hi,

I have an imported an excel spreadsheet and I have a column(A) that has a header with: Mail City/Mail State/Mail Zip within that column(A) the data reads: New Orleans LA 70122.

I need a formula that will take the text and place each item( 3 in this case, Mail City/Mail State/Mail Zip) in it's own seperate column with its designated info.

I know that this is a simple click "text to column" in excel but no so much in numbers

Thanx

pulphus wrote:

I know that this is a simple click "text to column" in excel but no so much in numbers

Two techniques for doing this, one with formulas, the other using a script, are described above. While I haven't tried Yvan's script, and can't speak to it, I can offer some comments on the formula example relative the the process in MS Excel (2007).

For your example (New Orleans LA 70122), fewer columns would be needed in Tables 2 and 3 than for the long original string in the OP's example.

Results for your example are essentially the same as in Excel—the four elements of the string are placed in four columns:

New | Orleans | LA | 70122

Both processes rely on recognizing one character as a separator to determine where to break the original data string, and both split New Orleans into two columns due to the internal space in that city's name.

The process in Excel (2007), while simpler than constructing the set of tables and formulas above, is not quite the "simple click" you describe unless the original data is in CSV format (commas separating the data elements). If, as in your example, the separator is a space, there are some decisions to be made. Here's the list of steps needed in Excel 2007:

Select the data.
Click the Data tab in the Toolbar.
Click Text to Table
In the first dialogue, check to see that the Text Wizard has correctly identified the data as "Delimited".
If the Delimited radio button is selected, Click Next
If not,click the Delimited radio button to change the data type from "Fixed width" to Delimited.
Click Next.
In the second dialogue, Click the Space checkbox to select the character to recognize as a separator.
Click Next
In the third dialogue, Select the column (4) that will hold the ZIP Code.
Click the Text radio button to change the format of this column from General to Text.
(For data sets larger than the single example, repeat the above step for every column into which the ZIP Code might be placed)
Click Finish.

Regards
Barry

Hi Barry,

I know how to do it in Excel 2007. Sorry for not being clear. My question is, what is the formula in Numbers that would allow me to achieve the same result if I were using Excel 2007? That apple script is so not gonna happen. I'm a avid apple user and I don't want to use/buy Excel 2011.

FYI, the original file that I'm sorting thru is a .csv file with 1250 mailing addresses. In one particular column, the Mail City/Mail State/Mail Zip are in one columns ,ie New Orleans LA 70122
I need said column separated in 3 or 4 separate columns .ie New | Orleans| LA | 70122 so that I can up load to mail service that send letters to the 1250 addresses.

While reading your post I was right with you until you gave the "how to" for Excel and not the formula in Numbers...

Thanking you in advance

Assuming that the original string is in B2 and that it may contain one, two, three or four "words" separated by space characters,
in C2, insert :
=IFERROR(LEFT(B2,SEARCH(" ",B2,1)-1),B2)

in D2, insert :
=IFERROR(IFERROR(LEFT(RIGHT(B2,LEN(B2)-1-LEN(C2)),SEARCH(" ",B2,LEN(C2)+2)-1-LEN(C2)),RIGHT(B2,LEN(B2)-1-LEN(C2))),"")

in E2, insert :
=IFERROR(IFERROR(LEFT(RIGHT(B2,LEN(B2)-1-LEN(C2&D2)),SEARCH(" ",B2,LEN(C2&D2)+3)-2-LEN(C2&D2)),RIGHT(B2,LEN(B2)-1-LEN(C2&D2))),"")

in F2, Insert :
=IFERROR(IFERROR(RIGHT(B2,LEN(B2)-2-LEN(C2&D2&E2)),RIGHT(B2,LEN(B2)-3-LEN(C2&D2) )),"")

I hope that I didn't left an error.

Yvan KOENIG (VALLAURIS, France) jeudi 4 novembre 2010 22:41:21

pulphus wrote:
I know how to do it in Excel 2007.
Sorry for not being clear.

You were clear. My listing of the necessary steps in Excel was in response to your comment, "I know that this is a simple click "text to column" in excel", and came after I fired up Excel to find out how 'simple' the process there was.

My question is, what is the formula in Numbers that would allow me to achieve the same result (as) if I were using Excel 2007?

The Text to Columns macro in Excel keeps the first element in the original cell and drops succeeding elements into cells in a series of columns. That "same result" isn't possible using a formula (in either application), as that would require the formula to act on and change the cell containing the original data.

The process for obtaining a similar result (each element of the string placed into a separate column of a table in the order it appears in the string) using formulas in Numbers has already been described in this thread.

The description is in my second post, which begins:

Beyond that, it gets a bit complicated.

Actually, it gets pretty simple,...



Original data goes into Table 1.
Table two calculates the break points (Use the version of the formula that ends with a LEN() statement).
Table 3 collects the final results.

All three tables need to have the same number of rows.
Table 1 requires only a single column.
Table 2 requires as many columns as necessary to get at least one 'empty' cell at the end of each row.
Table 3 requires the same number of columns as Table 2.

Once set up, using the document is pretty simple:

Copy the data from the Mail City/Mail State/Mail Zip column of the source.
Click on Table 1::A2
Paste.
Select the columns containing the separated elements in Table 3.
Copy
Click on the top left cell of the range in the table where you want the data to end up.
Go Edit > Paste Values.

Done

Note that the pasted values will overwrite data in the target cells. If you are pasting back into the original table, be sure you have inserted enough empty columns to accept the separated elements.

Regards,
Barry

Yvan, THANK YOU SO MUCH

that was it, it did the trick, now all we need to do is have my darling Apple at this either in an update or in the next version Wooohoo 🙂

Many thanks-worked precisely how described.