split one column to multiple columns

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

I have data formatted in column A like:

Sheldon Barnes <nhr4man@nc.rr.com>,
Miriam DURVAMC Mil34ler <miriam....miller@va.gov>,
Eddie Barnes <e.barne26s@verizon.net>,
Deloris Bell-Johnson <db12345ell67@nc.rr.com>,
Brenda Bethea <bbethea@email.un453c.edu>,
jessie bowen <jbowen8kdkdkd871@aol.com>,

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

Many Thanks in Advance
Greg
 
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
 
I have data formatted in column A like:

Sheldon Barnes <nhr4man@nc.rr.com>,
Miriam DURVAMC Mil34ler <miriam....miller@va.gov>,
Eddie Barnes <e.barne26s@verizon.net>,
Deloris Bell-Johnson <db12345ell67@nc.rr.com>,
Brenda Bethea <bbethea@email.un453c.edu>,
jessie bowen <jbowen8kdkdkd871@aol.com>,

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

Many Thanks in Advance
Greg
Click to expand...

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
 
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
 
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
 
Back
Top