Formula for "Text to Row" Functionality

[stwpkcmo]

All right . . .

I have been bashing my head against a wall for two days now because I got interested in solving this issue. I have a cell that contains thousands of names in the following format:

Smith MD, Bob; Savage DDS, Fred; Doe MD, Jane; . . . et cetera

I have already fixed the issue for my fellow associate (used text to column followed by a transpose to get the names into individual rows).

I know how to write the VB for this and I fixed the problem using the text to column method, so that leads me to the formula solution.

How can it be done . . . it's a challenge and I would like to solve this once and for all. As of now I have this formula:
=MID(A$1,ROW(),SEARCH(";",$A$1,1)-1)

This will return the first name and copy down, but the issue is the start value of one, this obviously needs to be the last semicolon "used" per se.

Any suggestions would be greatly appreciated

 

[Atroxell]

This is not a complete answer, but it might help give a kick start to you or someone else for an answer...

What if you try to use the find() function to define the start number in the MID() function? You might somehow be able to find the ";" in the string, then increment the search start for the MID() function?

Assuming your list is in A1:

in Cell A2:

=MID(A$1,1,SEARCH(";",$A$1,FIND(";",A$1,1))-1)

Cell A3 would look something like:

=MID(A$1,LEN(A2)+2,SEARCH(";",$A$1,FIND(";",A$1,LEN(A2))))

Unfortunately, I have been unable to make this work filling the formula down past A2, but someone might be able to expand on the answer...meanwhile, I am going to keep trying.

Edit: I just realized the reason why it's not working past A2--The formula needs to count the number of characters from the start of the original string to the last name pulled to define where i t needs to start looking for the next semi-colon. Right now it's just counting the most recent record length.