Text to Columns but using a formula

Originally Posted by kmham

I think this is very simple, but I'm stumped on what formula to use.

Say cell A1 has the following text: "It rained today"

I want "It" (the first word) to be in cell B2, "rained" to be in cell C2 and "today" to be in cell D2 - no quotes of course.

I do not want to use VBA/code please.

Manually I would normally use the Text to Columns feature under the Data menu, but special circumstances don't allow that this time (I need the text to be parsed automatically), so I must have formulas sitting in B2, C2 and D2 that automatically parse the words.

A max of words 3 will be in cell A1, A2, A3, etc. and the words will always have 1 space between them.

Please help. I really need this tonight.

Hi,

in cell B2

=LEFT(A2,FIND(" ",A2&" ")-1)

in cell C2

=MID(A2,FIND(" ",A2&" ")+1,FIND(" ",A2&" ",FIND(" ",A2&" ")+1)-1-FIND(" ",A2&" "))

in cell D2

=RIGHT(A2,LEN(A2)-FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

should do that. - note, I presume that you meant A2, and not A1.
---

This is almost exactly what I needed, but I should have been a bit clearer.

A1 can have up to 3 words, but it may only have 1 or 2 words.

Below are the scenarios. The problems are 1) "#value" showing in columns C & D when only 1 word is found in column A and 2) repeating the column C value in column D when there are only 2 words. Exactly 3 words works just fine.

A1..............B1.............C1..............D1
a...............a..............#value.........#value

A1..............B1..............C1..............D1
a b.............a................b................b

A1..............B1............C1..............D1
a b c..........a...............b................c

How can I modify this to accomplish each scenario using just 1 formula?

Thanks again for the help!!!

Originally Posted by kmham

This is almost exactly what I needed, but I should have been a bit clearer.

A1 can have up to 3 words, but it may only have 1 or 2 words.

Below are the scenarios. The problems are 1) "#value" showing in columns C & D when only 1 word is found in column A and 2) repeating the column C value in column D when there are only 2 words. Exactly 3 words works just fine.

A1..............B1.............C1..............D1
a...............a..............#value.........#value

A1..............B1..............C1..............D1
a b.............a................b................b

A1..............B1............C1..............D1
a b c..........a...............b................c

How can I modify this to accomplish each scenario using just 1 formula?

Thanks again for the help!!!

just do a count, and skip the second and third part

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<1,"",MID(A2,FIND(" ",A2&" ")+1,FIND(" ",A2&" ",FIND(" ",A2&" ")+1)-1-FIND(" ",A2&" ")))

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<2,"",RIGHT(A2,LEN(A2)-FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

etc

hth
---

Thanks again. Those last 2 formulas worked great. Could I get one more small (huge to me) favor?

The scope of my project changed slightly and my max is now 5 words instead of 3. I don't completely follow the 2 formulas that you've provided me, but I inserted them into my xls file and they both work just fine (for the 2nd and 3rd words).

I now need a formula for the 4th and 5th possible words.

I promise this is it - the max is 5 words!

I owe you big time Bryan!

Bryan - I had to make a slight change and I need your help again. Please my last comment above regarding the formula now needing to parse up to 5 values entered into Column A.

Originally Posted by kmham

Bryan - I had to make a slight change and I need your help again. Please my last comment above regarding the formula now needing to parse up to 5 values entered into Column A.

for more than 3 words, you need helper columns. The easy way is,

with your data in column A and columns B to P free,

in B1 put

=TRIM(MID(A1,FIND(" ",A1&" "),LEN(A1)))

and formula fill that to the right (say to column H

in I1 put

=TRIM(LEFT(A1,FIND(" ",A1&" ")))

and formula fill that to column P

then bulk-formula-fill B1:P1 down as far as your data extends (either select and formula drag, or, for a very large range, Select B1:P1, Copy, enter the range in the Name Box (B1:P40000) and press Enter to select the range, then Paste)

range I1 to whereever is your data, and this works on any number of words.

---

Originally Posted by kmham

You're a life saver. This worked perfectly!!!!!!

good to see that it worked for you, and thanks for the feedback
---

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.