Is there a formula/function to split data out of one cell?

I would like to amend my own post...

B2=IFERROR(REGEX.EXTRACT($A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*<?(.+)>?",1,1), "")

C2=IFERROR(REGEX.EXTRACT($A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*<?(.+)>?",1,2), "")

D2=IFERROR(REGEX.EXTRACT($A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*<?(.+)>?",1,3), "")

OK. I will explain a little about this approach...

There is another solution that uses string functions to locate the spaces between (the delimiter) each field. then you can use the left, mid and right functions to extract the fields. This option will not survive the what is there are two spaces or a tab.

The solution I am presenting uses "REGULAR EXPRESSIONS" for pattern matching in text.

Now I will break it into pieces:

"([A-Za-z]+) \s* ([A-Za-z]+) \s* <? (.+) >? "

([A-Za-z]+) means to look for 1 or more characters in the set A-Z, a-z. the parenthesis mean to capture the result in place 1

the \s* means to look for zero or more white space characters (white space is a space or tabs)

<? (.+) >? means to look for one or more of any character (the dot) surrounded by zero or one angle braces

Try this... assuming that the data is in column A as shown in the following table:

select cell B2 and type (or copy and paste from here) the formula:

=REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,1)

shorthand for this is:

B2=REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,1)

then type return.

C2=REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,2)

D2=SUBSTITUTE(SUBSTITUTE(REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,3), ">", ""), "<", "")

To fill down:

select cells B2 thru D2, copy

select cells C2 thru the end of column D, paste

if you do not want to see errors for empty cells modify the formulas to "mask out" these errors use the following formulas:

B2=IFERROR(REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,1), "")

C2=IFERROR(REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,2), "")

D2=IFERROR(SUBSTITUTE(SUBSTITUTE(REGEX.EXTRACT(A2,"([A-Za-z]+)\s*([A-Za-z]+)\s*(.+)",1,3), ">", ""), "<", ""), "")

Then fill down using the copy and paste I presented earlier

Hi Judy,

I am having trouble visualising:

Column A

first name1 last name1 <email address1>

first name2 last name2 <email address2>

etc.

And then: Peyton M <***.com>

A picture paints a thousand words. Please post a screen shot of a small part of the cell (A2?) that contains all the data that you pasted.

Regards,

Ian.

Are you saying that all the data is stored in a single cell?

Like I have shown in cell A7 as compared to Cells A2 thru A5?

Please share a little more how you acquired the data. Go back to the source, select all the data, copy.

Now come back to Numbers and select cell A2 in NEW Numbers table-- select by single clicking the cell.

Now paste

Judy,

the screenshot is a little helpful.

What I think is the following:

1) the contact info in the email is something like:

first1 last1 <email1>_TAB_first2 last2 <email2>_TAB_first3 last3 <email3>_TAB_first4 last4 <email4>

I know that may look complicated...

• the fields within each group are separated (delimited) by spaces
• each contact is separated (delimited) by tabs and
• there are no line ending characters (return)

copy the list of emails from original email

paste into the app Text Edit

locate the first TAB (highlighted in grey in my screenshot)

select a TAB, copy

In the Find (menu item "Edit > Find and Replace…")

paste the tab you copied into the "Find" field

select that same TAB, type the return key

hold the shift key and type the back arrow once to select the RETURN, copy

in the Replace field, paste

click the segment "ALL"

select all and paste into Numbers:

that should do it

Judy,

I you do post a screenshot... please do not post peoples names and email addresses. It should be a representative example.

ledzep.

I like Toto and the interesting fact is that John Bonham was partly the inspiration for the "Rosanna shuffle"

https://www.youtube.com/watch?v=NMI81yIlT0Q

You're welcome