split column using formula to keep it dynamic

[Bob_ipc]

Hey thanks for looking

In column F I have between 1-3 words, when there are three words I would like them to go to column G and H using a formula, The data comes from another tab so I would like it to load dynamically. Any help would be great!! Thank you.

 

[estevaoba]

Hi!

Assuming your words are separated by spaces, I would use a help column, say L, with this formula:

L1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=MID(F1,SEARCH("  ",F1)+1,50)[/TD]
[/TR]
</tbody>[/TABLE]

Then I would use these to get single words in G thru H;

G1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=LEFT(F1,SEARCH("  ",F1)-1)[/TD]
[/TR]
</tbody>[/TABLE]

H1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF(ISERR(SEARCH("  ",F1,SEARCH(" ",F1)+1)),MID(F1,SEARCH("  ",F1)+1,50),LEFT(L1,SEARCH(" ",L1)-1))[/TD]
[/TR]
</tbody>[/TABLE]

I1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF(ISERR(SEARCH("  ",F1,SEARCH(" ",F1)+1)),"",MID(L1,SEARCH("  ",L1)+1,50))[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!

 

[Bob_ipc]

Hey thanks for the response, I gave it a try using GZ column as my help column cause that was my next empty column, but I was only getting #VALUE Errors. What am I missing?

 

[estevaoba]

It could be MS Excel version. Which one is yours?
Mine is 2016 and it works fine.
I have uploaded my workbook here, take a look:

https://we.tl/d7C04s89lr

 

[Bob_ipc]

My worksheet has blanks, would that be the dirty culprit?

 

[Bob_ipc]

And I also have 2016

 

[estevaoba]

Try these (no help column needed):

G1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(IFERROR(LEFT(F1,SEARCH("  ",F1)-1),LEFT(F1,FIND("/",F1,1)-1)),"")[/TD]
[/TR]
</tbody>[/TABLE]

H1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(IFERROR(LEFT(MID(F1,IFERROR(FIND("  ",F1,LEN(IFERROR(LEFT(F1,SEARCH("  ",F1)),LEFT(F1,FIND("/",F1,1)-1))))+1,FIND("/",F1,LEN(IFERROR(LEFT(F1,SEARCH("  ",F1)),LEFT(F1,FIND("/",F1,1)-1))))+1),20),FIND("  ",MID(F1,IFERROR(FIND(" ",F1,LEN(IFERROR(LEFT(F1,SEARCH("  ",F1)),LEFT(F1,FIND("/",F1,1)-1))))+1,FIND("/",F1,LEN(IFERROR(LEFT(F1,SEARCH("  ",F1)),LEFT(F1,FIND("/",F1,1)-1))))+1),20),1)-1),MID(F1,IFERROR(FIND("  ",F1,LEN(IFERROR(LEFT(F1,SEARCH("  ",F1)),LEFT(F1,FIND("/",F1,1)-1))))+1,FIND("/",F1,LEN(IFERROR(LEFT(F1,SEARCH("  ",F1)),LEFT(F1,FIND("/",F1,1)-1))))+1),20)),"")[/TD]
[/TR]
</tbody>[/TABLE]

I1[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F1,G1,""),H1,""),"/",""),"  ",""),"")[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!