Seperating Text String

[Gian624]

Hello Everyone,

I have a single text string of names from Outlook seperated by a semi-colon that are in one cell in excel. I am looking for a formula to seperate those 30 plus names into seperate cells. Does anyone have a formula that will do that?

eg: John Smith; Jane Doe; John Doe; Jane Smith

John Smith
Jane Doe
John Doe
Jane Smith

Thank you,
Matt

 

[s.ridd]

Would Using Text To Columns with a semicolon delimiter and then copy and paste special with the transpose option selected satisfy your needs? Avoids complex formulas or VBA

 

[Snakehips]

Matt,

Her are options but both involve use of vba.

1......
Copy this code to the sheet module. Assuming your string is in A1 it will create a column list starting in A1

Sub Sep_Names()
arr = Split(ActiveSheet.Range("A1"), ";")
For r = LBound(arr) To UBound(arr)
Cells(r + 1, 1).Value = Trim(arr(r))
Next r
End Sub

or 2.......

Paste this function into a code Module ( F11 Insert Module ) and then use a formula eg as below.

Function SepNames(Str As String, Prt As Integer)
SepNames = Trim(Split(Str, ";")(Prt))
End Function

Excel 2007
	A
1	Jane Doe;Fred Jones ; John Smith
2	Jane Doe
3	Fred Jones
4	John Smith
5
Sheet1

Cell Formulas
Range	Formula
A2	=IFERROR(SepNames($A$1,ROWS(A$1:A1)-1),"")

Hope that helps.

 

[Rick Rothstein]

Hello Everyone,

I have a single text string of names from Outlook seperated by a semi-colon that are in one cell in excel. I am looking for a formula to seperate those 30 plus names into seperate cells. Does anyone have a formula that will do that?

eg: John Smith; Jane Doe; John Doe; Jane Smith

John Smith
Jane Doe
John Doe
Jane Smith

Thank you,
Matt

Assuming your list has a space after each semi-colon and is located in cell A1, put this formula in cell A2 and copy down for as many rows as you think you will ever need...

=TRIM(MID(SUBSTITUTE(A$1,"; ",REPT(" ",300)),ROW(A1)*300-299,300))

 

[Snakehips]

Assuming your list has a space after each semi-colon and is located in cell A1, put this formula in cell A2 and copy down for as many rows as you think you will ever need...

=TRIM(MID(SUBSTITUTE(A$1,"; ",REPT(" ",300)),ROW(A1)*300-299,300))

Rick,

When you use the above structure it requires that you substitute an adequate number of spaces, otherwise it will error when parsing the back end of the string or may error on a long sub-string.

Would you please tell me.......
Do you have a rule of thumb for determining how many spaces you insert? (Substrings*Expected length?)

Is there a limit beyond which you could not employ this approach?