Text to Columns via formula?

[TWTHOMAS]

Greetings,

I searched the forums and found several answers to this but have not found one that works the way I need it to.

I have a list of names that I import almost weekly.

I have to break these names into columns but really don't want to have to use text to coumns each time. This is because in reality I don't want to past the data into my primary work area since text to coumns tends to expand to make room.

What I would like to do is paste my list into Sheet two for example and then have formulas in sheet 1 that would extract the names.

The issue I am running into is that the names are variable.
For example I might have:

Smith John Thomas
Jones Thomas Howell III
Thompson Sue
Padget Eugene Rex Michael

These are always broken by spaces without exception.

I would like to find a way to place formulas in sheet one that would exmain column A in sheet two and split the names into however many columns are needed. The most I have ever found is six.

Does this make sense? If so, does anyone know a way?


Thanks in advance as usual.

 

[Capt Fantastikk]

I ran into the same issue with names. Basically trying to do a Text to Column with a formula. The issue I'm running into is getting the comma to not show up.

What I am trying to convert is: 'Last, First'

I've found two different ways to do it and got two different results.

The way mentioned above"=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))" seems to work the best, but the last name has a comma after it.

Another way is using the LEFT MID and RIGHT function, but there are times that it is two names and times that it is 3. That's why the above works the best.

How do I get the above formula to calculate out the comma?

 

[Jonmo1]

What I am trying to convert is: 'Last, First'

You should really start a New Thread.

And you should also indicate what you want to convert it TO

 

[barneyaj]

I have a similar issue hope you can help. I'm compiling a list of information and having to save the URL. I would like to extract a number (587534) out of a URL (I modified the thread URL as an example):
http://www.mrexcel.com/forum/excel-questions.587534.text-columns-via-formula.html

I think the same rules can apply as the first half of the URL is always the same but I can't figure out how to adapt your formula to suit the text.

 

[barneyaj]

Just a bit extra info, the first half of the URL is always the same "http://www.mrexcel.com/forum/excel-questions."
The number is variable length, the text after the number is not the same.

 

[barneyaj]

OK - I stopped being lazy (sort of) and found the answer - I have to thank the previous post:
http://www.mrexcel.com/forum/excel-questions/444266-extract-string-between-two-characters.html

Finally this was the formula that worked for me:
=LEFT(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))-FIND(".",RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))),FIND(".",RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))-FIND(".",RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))))-1)

If there's a simpler one then please post it please.

 

[jrpotts]

You Sir, are a genius. Thank you.

Are you able to let me know what the columns part and the -1 and +1 parts were for in the initial formula? I couldn't quite work out that bit.
I would have thought that COLUMNS($A1:A1) would always give a value of "1".
And why are you adding all the spaces in there? I have spent some time trying to work out this formula and I don't quite get it.

Thanks for your help.

Any advice if the data set has a mix of spaces and commas?

e.g. lastname, firstname middlename

 

[navees]

Wow, that was amazing, Thank you soo much Sir. i created account only to thank you :D, you made my work a lot easy.

 

[addepallibabu]

Try

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))

Filled right as far as needed.

Sir, This one is terrific. Thank you for the solution.

I have different problem with multiple delimits

Ex:

Smith/John*Thomas
Jones/Thomas*Howell-III
Thompson/Sue
Padget/Eugene*Rex-Michael



and the result should be as below with separate columns

Smith John Thomas
Jones Thomas Howell III
Thompson Sue
Padget Eugene Rex Michael

Please provide the solution. Thanks in advance.

 

[Trik86]

You Sir, are a genius. Thank you.

Are you able to let me know what the columns part and the -1 and +1 parts were for in the initial formula? I couldn't quite work out that bit.
I would have thought that COLUMNS($A1:A1) would always give a value of "1".
And why are you adding all the spaces in there? I have spent some time trying to work out this formula and I don't quite get it.

Thanks for your help.

The spaces are there as a loop that ensures that the whole word comes through, no matter how many delimiters/columns you need. The TRIM then eliminates any excess spaces. You can mark subformulas and press F9 to see what they return. This way you can see the results from individual parts and understand how they work together.

Hope it helps.

 

[Trik86]

Sir, This one is terrific. Thank you for the solution.

I have different problem with multiple delimits

Ex:

Smith/John*Thomas
Jones/Thomas*Howell-III
Thompson/Sue
Padget/Eugene*Rex-Michael



and the result should be as below with separate columns

Smith John Thomas
Jones Thomas Howell III
Thompson Sue
Padget Eugene Rex Michael

Please provide the solution. Thanks in advance.

I would use the SUBSTITUTE formula to convert the "/" and "*" into " " (empty space). For example =SUBSTITUTE(Smith/John*Thomas,"/"," "). You could use this formula in several steps to first convert "/" to " ". Then again to convert "*" to " ".

Then use the nice formula given above:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))