Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

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!

Find text within text
 

I'm afraid, most definitely, that the names are as stated. HONEST <g
First Last1; First Last2; First Last3;
First name, followed by a space, followed by the last name, followed by a
semi-colon, followed by a space, then the next name.
There is a space between "First" and "Last". Does this make a difference?

Thanks for the info on "|". It begins to make sense. Soon I will be a
master, too! <g

"Rick Rothstein (MVP - VB)" wrote:

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!

Find text within text
 

I'm not sure what to tell you... I pasted your text into G1 exactly as
posted (without the trailing semi-colon/space at the end that you show
though) and the formula I posted correctly separates the full names (no
missing first letters), one to a row. Did you copy/paste my formula or try
to key it in by hand?

And, no, the space between the first and last names is immaterial (only the
semi-colon/space combinations are important).

Rick


"Hugh Murfitt" wrote in message
...
I'm afraid, most definitely, that the names are as stated. HONEST <g
First Last1; First Last2; First Last3;
First name, followed by a space, followed by the last name, followed by a
semi-colon, followed by a space, then the next name.
There is a space between "First" and "Last". Does this make a difference?

Thanks for the info on "|". It begins to make sense. Soon I will be a
master, too! <g

"Rick Rothstein (MVP - VB)" wrote:

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!

Find text within text
 

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

Find text within text
 

Ummm, hang my head in shame :(
I copied the formula, but managed to copy the line break before the last
"&$G$1 too. Now that it's gone, your formula works perfectly. Sorry for not
seeing it sooner.
And YOU, Mr Rick Rothstein, get my vote for the most helpful MVP ever!!
Thanks very much.

"Rick Rothstein (MVP - VB)" wrote:

I'm not sure what to tell you... I pasted your text into G1 exactly as
posted (without the trailing semi-colon/space at the end that you show
though) and the formula I posted correctly separates the full names (no
missing first letters), one to a row. Did you copy/paste my formula or try
to key it in by hand?

And, no, the space between the first and last names is immaterial (only the
semi-colon/space combinations are important).

Rick


"Hugh Murfitt" wrote in message
...
I'm afraid, most definitely, that the names are as stated. HONEST <g
First Last1; First Last2; First Last3;
First name, followed by a space, followed by the last name, followed by a
semi-colon, followed by a space, then the next name.
There is a space between "First" and "Last". Does this make a difference?

Thanks for the info on "|". It begins to make sense. Soon I will be a
master, too! <g

"Rick Rothstein (MVP - VB)" wrote:

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!

Find text within text
 

Ummm, hang my head in shame :(
I copied the formula, but managed to copy the line break before
the last "&$G$1 too. Now that it's gone, your formula works
perfectly. Sorry for not seeing it sooner.

Whew! I'm glad you got that straightened out... I wasn't sure what to tell
you to do next.

And YOU, Mr Rick Rothstein, get my vote for the most helpful
MVP ever!! Thanks very much.

You are quite welcome; although I'd like to say that you are placing me on
much too high a pedestal here. The other MVPs, as well as the other regular
volunteers who have not yet been recognized with an MVP designation, are a
wonderful collection of the most helpful people I have ever seen. If anyone
from this group is to be tagged as the "most helpful ever", believe me when
I tell you that I am not the person who that should be.

Rick

Find text within text
 

Well thanks anyway!

"Rick Rothstein (MVP - VB)" wrote:

Ummm, hang my head in shame :(
I copied the formula, but managed to copy the line break before
the last "&$G$1 too. Now that it's gone, your formula works
perfectly. Sorry for not seeing it sooner.

Whew! I'm glad you got that straightened out... I wasn't sure what to tell
you to do next.

And YOU, Mr Rick Rothstein, get my vote for the most helpful
MVP ever!! Thanks very much.

You are quite welcome; although I'd like to say that you are placing me on
much too high a pedestal here. The other MVPs, as well as the other regular
volunteers who have not yet been recognized with an MVP designation, are a
wonderful collection of the most helpful people I have ever seen. If anyone
from this group is to be tagged as the "most helpful ever", believe me when
I tell you that I am not the person who that should be.

Rick