I have a list of names, each separated by a semi-colon and a space, which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3; First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the result.
Please help!

The easiest way is to highlight the column, DATA- Text to Columns choose
delimited and hit next, then check the semicolon box and finish, it will
cause a cell split at each ;
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space, which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3; First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the result.
Please help!

Thanks but I was hoping for something a bit more sophisticated. I will
change cell A1 often and hoped to get the columnar results automatically.
Also, macros at work often get blocked.
Any other suggestions?

"John Bundy" wrote:

The easiest way is to highlight the column, DATA- Text to Columns choose
delimited and hit next, then check the semicolon box and finish, it will
cause a cell split at each ;
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space, which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3; First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the result.
Please help!

Give this formula a try...

=IF(ROWS($1:1)1+LEN($G$1)-LEN(SUBSTITUTE($G$1,";","")),"",SUBSTITUTE(SUBSTIT UTE($G$1,MID($G$1,FIND("|",SUBSTITUTE($G$1&";",";" ,"|",ROWS($1:1))),1000),""),LEFT($G$1,FIND("|",SUB STITUTE(";
"&$G$1&";",";","|",ROWS($1:1)))-1),""))

Place it in A1 and copy down as far as you would like.

Rick


"Hugh Murfitt" wrote in message
...
Thanks but I was hoping for something a bit more sophisticated. I will
change cell A1 often and hoped to get the columnar results automatically.
Also, macros at work often get blocked.
Any other suggestions?

"John Bundy" wrote:

The easiest way is to highlight the column, DATA- Text to Columns choose
delimited and hit next, then check the semicolon box and finish, it will
cause a cell split at each ;
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space,
which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3;
First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the
result.
Please help!

Almost there. The first result is correct but the rest have their first
letter missing.
The formula is too complex for me to tweak correctly!
What is "|"?
Many thanks, Rick

"Rick Rothstein (MVP - VB)" wrote:

Give this formula a try...

=IF(ROWS($1:1)1+LEN($G$1)-LEN(SUBSTITUTE($G$1,";","")),"",SUBSTITUTE(SUBSTIT UTE($G$1,MID($G$1,FIND("|",SUBSTITUTE($G$1&";",";" ,"|",ROWS($1:1))),1000),""),LEFT($G$1,FIND("|",SUB STITUTE(";
"&$G$1&";",";","|",ROWS($1:1)))-1),""))

Place it in A1 and copy down as far as you would like.

Rick


"Hugh Murfitt" wrote in message
...
Thanks but I was hoping for something a bit more sophisticated. I will
change cell A1 often and hoped to get the columnar results automatically.
Also, macros at work often get blocked.
Any other suggestions?

"John Bundy" wrote:

The easiest way is to highlight the column, DATA- Text to Columns choose
delimited and hit next, then check the semicolon box and finish, it will
cause a cell split at each ;
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space,
which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3;
First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the
result.
Please help!

Almost there. The first result is correct but the rest have their first
letter missing.

Then you lied to us.<g You said "I have a list of names, each separated by
a semi-colon and a space"... what you are seeing would happen only if the
space was not following the semi-colon. I can modify the formula, but first
tell me what your actual condition is.

The formula is too complex for me to tweak correctly!
What is "|"?

It is just a character that I expect never to be in your text. What I am
doing is finding the appropriate semi-colon/space and replacing it with the
vertical bar so that I can later find it... this allows me to zero in on the
correct portion of the string.

Rick


Many thanks, Rick

"Rick Rothstein (MVP - VB)" wrote:

Give this formula a try...

=IF(ROWS($1:1)1+LEN($G$1)-LEN(SUBSTITUTE($G$1,";","")),"",SUBSTITUTE(SUBSTIT UTE($G$1,MID($G$1,FIND("|",SUBSTITUTE($G$1&";",";" ,"|",ROWS($1:1))),1000),""),LEFT($G$1,FIND("|",SUB STITUTE(";
"&$G$1&";",";","|",ROWS($1:1)))-1),""))

Place it in A1 and copy down as far as you would like.

Rick


"Hugh Murfitt" wrote in message
...
Thanks but I was hoping for something a bit more sophisticated. I will
change cell A1 often and hoped to get the columnar results
automatically.
Also, macros at work often get blocked.
Any other suggestions?

"John Bundy" wrote:

The easiest way is to highlight the column, DATA- Text to Columns
choose
delimited and hit next, then check the semicolon box and finish, it
will
cause a cell split at each ;
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space,
which I
have pasted into a cell (eg G1 = First Last1; First Last2; First
Last3;
First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the
result.
Please help!

You could write a macro - the following works:

Sub names()

Dim i As Integer
i = 1

With Range("a1")
Do While Not InStr(.Value, ";") = 0
.Offset(i, 0).Value = Left(.Value, InStr(.Value, ";") - 1)
.Value = Right(.Value, Len(.Value) - InStr(.Value, ";"))
i = i + 1
Loop

End With

End Sub


"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space, which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3; First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the result.
Please help!

You could use Data TextToColumns to separate the names out into columns,
and then use Copy PasteSpecial Transpose to convert them to rows down one
column.

Vaya con Dios,
Chuck, CABGx3




"Hugh Murfitt" wrote:

I have a list of names, each separated by a semi-colon and a space, which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3; First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the result.
Please help!

On Thu, 4 Oct 2007 06:44:00 -0700, Hugh Murfitt
wrote:

I have a list of names, each separated by a semi-colon and a space, which I
have pasted into a cell (eg G1 = First Last1; First Last2; First Last3; First
Last4; First Last5)
How do I split out the names into a column so that
A1 = First Last1
A2 = First Last2
A3 = First Last3?
I've used combinations of Find, Mid, Len but I just can't nail the result.
Please help!

Here is a UDF that will extract the names.

To use it in your example, enter

A1: =TRIM(reextr($G$1,"[^;]+",ROWS($1:1)))

Fill down as far as required.

To enter the UDF

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens:

=====================================
Option Explicit

Function reExtr(str, sPattern As String, Optional Index As Long = 1) As String
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern

If re.test(str) = True Then
Set mc = re.Execute(str)
If mc.Count = Index Then
reExtr = mc(Index - 1)
End If
End If

End Function
============================

Let me know if this works for you.
--ron