Breaking up string of text in 1 cell into multiple cells

I'm sure this has been asked, but the search function is failing me right now (not bringing up any results).

I have a string of data that I copied from a pdf file into excel. All the data falls into 1 cell and I need to break up the data into multiple cells (multiple fomulas/VBA across multiple cells) to ensure that the correct data ends up in the correct cell/column.

Sample String
Last, First Middle (2731715) 111-222-3333 [email protected]
Last, First [no-middle] (3053815) 111-222-4444 [email protected]

All this data is in cell A1.

The following cells are what pieces of information need to be populated into them.

B1 - Last Name (no comma)
C1 - First Name
D1 - Middle Name
E1 - String of Number with out the parenthesis
F1 - Phone Number
G1 - Email address

I know for cell B1, I can use the formula =LEFT(A1,FIND(",",A1,1)-1) and it'll pull all the text prior to the comma.

For cell C1, what makes sense in my [crazy] head would be to Find all text after what's in cell B1 up until the next "space" character.

For cell D1, find all text after what's in cell C1 up until the next space character.

For cell E1, find all text after what's in cell D1 and eliminating the parenthesis and only displaying the numbers

For cell F1 - etc

Cell G1 - etc

I'm really good at copying and pasting VBA/Macros along with formula's into Excel. I can typically understand formulas but right now I'm having a rough time comprehending what needs to be done.

Any help? Thanks folks!

Try this code:
Column B1

Please Login or Register  to view this content.

Column C1

Please Login or Register  to view this content.

Column D1

Please Login or Register  to view this content.

Column E1

Please Login or Register  to view this content.

Column F1

Please Login or Register  to view this content.

Column G1

Please Login or Register  to view this content.

Column H1

Please Login or Register  to view this content.

Regards,
SDCh

If you are using 2003 as your profile suggests, I think you will need a helper column to change the string if there is no middle name

In B2, drag/Fill Down. (This can be hidden with the +/- grouping button.)

Formula:

Please Login or Register  to view this content.

Then in C2, Drag Across and down

Formula:

Please Login or Register  to view this content.

If you are using a later version of Excel, you could replace $B2 with the helper formula, then the helper wouldn't be required.

The "all in one" formula is to involved for 2003.

@Marcol: Nice and simple formula I get new knowledge from this.

If you don't mind, we use the formula from Marcol with little change if you not want a helper column. Just drag across and down.

Please Login or Register  to view this content.

If you not want has "-" on middle name use this:

Please Login or Register  to view this content.

Note : Thanks to Marcol to show this formula No need to think and write different formula on every cell

If we introduce a named range then the problem can be further simplified.

Name:= FullString
Refers to:=

Formula:

Please Login or Register  to view this content.

Then in B2, Drag Across and Down

Formula:

Please Login or Register  to view this content.

This should work for all versions of Excel