Find text within text

[Guest]

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!

 

[Guest]

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 ;

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

 

[Rick Rothstein \(MVP - VB\)]

Give this formula a try...

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

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

Rick

 

[Guest]

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\)]

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

 

[Guest]

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\)]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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\)]

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

 

[Guest]

Well thanks anyway!

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


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