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