split one column to multiple columns

[Greg]

I have data formatted in column A like:

Sheldon Barnes <[email protected]>,
Miriam DURVAMC Mil34ler <[email protected]>,
Eddie Barnes <[email protected]>,
Deloris Bell-Johnson <[email protected]>,
Brenda Bethea <[email protected]>,
jessie bowen <[email protected]>,

How can i separate into three columns?
First Name, Last Name, E-mail

Many Thanks in Advance
Greg

 

[L. Howard Kittle]

Hi Greg,

See if Text To Column under Data will do that for you. Use Space as the
Delimiter.

It worked for me on a simple test of your data but did leave the "<" in
front of the e-mail address. Perhaps a remove > replace "< " replace with
nothing > Ok will take care of that.

Also, the last name is left underlined and in blue font color, a couple of
steps to clear that up will be in order. Select and remove the underline
and while selected return font color to Automatic.

HTH
Regards,
Howard

 

[David Biddulph]

What did that method do with middle names, as in the second entry in the
OP's list of examples?

 

[Ron Rosenfeld]

I have data formatted in column A like:

Sheldon Barnes <[email protected]>,
Miriam DURVAMC Mil34ler <[email protected]>,
Eddie Barnes <[email protected]>,
Deloris Bell-Johnson <[email protected]>,
Brenda Bethea <[email protected]>,
jessie bowen <[email protected]>,

How can i separate into three columns?
First Name, Last Name, E-mail

Many Thanks in Advance
Greg

These formulas rely on the following:

1. First Name is first word
2. Last Name is the last word before the "<"
3. Email is at the end and enclosed "<...>"

With data in A2:

B2 (FN): =LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)

C2 (LN):
=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,FIND("<",A2)-1))," ",REPT(" ",99)),99))

D2 (EMAIL):
=MID(TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)),2,
FIND(">",TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)))-2)

Or you could use a VBA Macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells to be parsed.
Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

==================================================
Option Explicit
Sub parseFNLNEmail()
Dim re As Object, mc As Object
Dim rg As Range, c As Range
Dim i As Long, s As String

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\w+).*\s(\S+)\s.*<([^>]*)"

Set rg = Selection 'or set up however is most efficient
For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 3)).ClearContents
s = c.Value
If re.test(s) = True Then
Set mc = re.Execute(s)
For i = 1 To 3
c.Offset(0, i).Value = mc(0).submatches(i - 1)
Next i
End If
Next c
End Sub
========================================================
--ron

 

[Greg]

Thanks, the text to column worked great!. i thought i had used that function
before but couldn't remember where it was at.

Thanks Again.
Greg

 

[L. Howard Kittle]

I missed that one, and as you would expect it went to four columns on that
entry.

Perhaps a cleaner way would have been use "<" as the delimiter for one try
and then use space on the next try (need to move some data about to make
room for the second split. Then use =LEFT(F9,LEN(F9)-2) on the address to
clean up the >, at the end.

Regards,
Howard