HOW TO SPLIT PARTS OF TEXT STRING INTO 6 COLUMNS

[JuicyMusic]

I have tried and tried. I can adjust these types of formula's (somewhat) if I need to......but I just can't figure this one out.

Normal request: How to extract text after the last space in a string? I can do this.
Unusual: How to extract text after the space BEFORE the last space in a string. I CAN'T DO THIS ONE

In a nut shell.....I need to split a string into 6 parts, and these 6 parts going into a different column on the same row.
See the image I uploaded. The spacing or order of information is always constant and won't change.

Column C : Original String
See the 2nd image uploaded to see what type of text goes into column D thru I. See my red text where I explain. TYSM!!!!!


Here is a string for example.

GB-200 130.00 EA 54" Freestanding Vanity $63,830.00 3000.030 Casegoods

 

[JuicyMusic]

...Do you mean "go to"?

 

[JuicyMusic]

Hi there, I did change the number at the end to 2,3,4,5,6. No results. Just #NAME?.

 

[Herakles]

 

[Tom.Jones]

Try this:

=INDEX(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE($A2,"0 ","0 /<>"),"$","/<>$"),"/<>"),{1,3,2,4,5,6})

 

[Herakles]

Thanks Tom.

I just learnt about that trick from Leila Gharani on one of her posts on www.xelplus.com.

Excel Filter Function Trick Using Non-Adjacent Columns

Are you up for a really cool trick with Excel's new FILTER function? With this, you can get multiple match results from non-adjacent columns in a simple, dynamic way! Check out the full article.

www.xelplus.com

 

[Fluff]

I put the formula in ONLY cell D2....and tried putting the formula in cells D2 to I2

The formula should go in D2 only & then drag down.

 

[JuicyMusic]

The formula should go in D2 only & then drag down.

H Fluff, I placed formula in D2 and dragged it down. I'm still getting a #NAMED error.

 

[Fluff]

Do you have the textsplit, textbefore & textafter functions?

 

[JuicyMusic]

Hi @JuicyMusic

Your data seems to be inconsistent at line 3 where there is no space between "GB-201" and "7.00".
Is that a typo?

If your data is inconsistent, it will be difficult to handle.
Meanwhile, here's a table with formulas that should work on your example string:

Book1.xlsm
	C	D	E	F	G	H	I
1	ORIGINAL TEXT	HPGI Code	Description	Count	Total Cost	PH Code	Type
2	GB-200 130.00 EA 54" Freestanding Vanity $63,830.00 3000.030 Casegoods	GB-200	EA 54" Freestanding Vanity	130.00	$63,830.00	3000.030	Casegoods
Sheet35

Cell Formulas
Range	Formula
D2	D2	= LEFT(C2,FIND(" ",C2)-1)
E2	E2	= MID(C2,FIND(" ",C2,FIND(" ",C2)+1),FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )-2))-FIND(" ",C2,FIND(" ",C2)+1))
F2	F2	= MID(C2,FIND(" ",C2)+1,FIND(" ",C2,FIND(" ",C2)+1)-FIND(" ",C2)-1)
G2	G2	= MID(C2,FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )-2))+1,FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )-1))-FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )-2)))
H2	H2	= MID(C2,FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )-1))+1,FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )))-FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )-1)))
I2	I2	= MID(C2,FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") ))),LEN(C2)-FIND("~",SUBSTITUTE(C2," ","~",LEN(C2) - LEN( SUBSTITUTE(C2," ","") )))+1)

PeteWright, this worked perfectly! I've been waiting to see the solution so I could ask a few questions.......but these formulas are so pretty but looking at them tells me nothing.. Do you happen to know if there are online classes specifically for splitting text? I would love to be adept. TY for the solution. Juicy

 

[JuicyMusic]

Do you have the textsplit, textbefore & textafter functions?

Yes I do.