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

 

[PeteWright]

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)

 

[Herakles]

Without a good set of representative data it is difficult to carry out a full test but I've tried this and it may help.

=INDEX(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE($B3,"0 ","0 /<>"),"$","/<>$"),"/<>"),1)

where B3 contains the source text.

I've used "/<>" as a character combination to delimit the elements but use whatever will never be found in the source text.

The last value, 1 in this case, indicates the element number to return.

Put this formula in the 6 columns and just indicate which element you want to return.

 

[PeteWright]

=INDEX(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE($B3,"0 ","0 /<>"),"$","/<>$"),"/<>"),1)

Wow! Never thought it would be possible with one formula.

I will keep this in mind for my future projects where there is a lot of data to "split".

Still got a lot to learn

 

[Herakles]

I learn something new every day by tackling posts on Mr Excel.

One good approach I take is to turn the source data into something that can be worked on using standard Excel functions.

I often use multiple SUBSTITUTE for this but without a lot of data one can come unstuck.

If JuicyMusic needs this to be tweaked I would not be surprised.

 

[Fluff]

Another option which assumes there will always be a space between each element.

Fluff.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
Master

Cell Formulas
Range	Formula
D2:I2	D2	=LET(s,TEXTSPLIT(C2," "),HSTACK(INDEX(s,,1),TEXTJOIN(" ",,DROP(DROP(s,,2),,-3)),INDEX(s,,2),CHOOSECOLS(s,-3),CHOOSECOLS(s,-2),CHOOSECOLS(s,-1)))
Dynamic array formulas.

 

[JuicyMusic]

Without a good set of representative data it is difficult to carry out a full test but I've tried this and it may help.

=INDEX(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE($B3,"0 ","0 /<>"),"$","/<>$"),"/<>"),1)

where B3 contains the source text.

I've used "/<>" as a character combination to delimit the elements but use whatever will never be found in the source text.

The last value, 1 in this case, indicates the element number to return.

Put this formula in the 6 columns and just indicate which element you want to return.

Hi there, I just noticed that just now. Someone made a typo

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)

Hi there, I just noticed that. That is certainly a typo and you are correct. It has to be standard. This is the first time I've seen a typo on my data.

 

[JuicyMusic]

Without a good set of representative data it is difficult to carry out a full test but I've tried this and it may help.

=INDEX(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE($B3,"0 ","0 /<>"),"$","/<>$"),"/<>"),1)

where B3 contains the source text.

I've used "/<>" as a character combination to delimit the elements but use whatever will never be found in the source text.

The last value, 1 in this case, indicates the element number to return.

Put this formula in the 6 columns and just indicate which element you want to return.

Hi Herackles, I put the formula in all 6 columns but I'm not sure what to change.

 

[Herakles]

Hi there, I just noticed that just now. Someone made a typo

Hi Herackles, I put the formula in all 6 columns but I'm not sure what to change.

You change the 1 at the end to indicate which source column you want the data to come from.

 

[JuicyMusic]

Another option which assumes there will always be a space between each element.

Fluff.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
Master

Cell Formulas
Range	Formula
D2:I2	D2	=LET(s,TEXTSPLIT(C2," "),HSTACK(INDEX(s,,1),TEXTJOIN(" ",,DROP(DROP(s,,2),,-3)),INDEX(s,,2),CHOOSECOLS(s,-3),CHOOSECOLS(s,-2),CHOOSECOLS(s,-1)))
Dynamic array formulas.

Fluff!!.....I'm so happy to see your response. Yes, there will always be a space between elements.
It's not working. You're first snapshot is exactly the results I'm looking for.
I put the formula in ONLY cell D2....and tried putting the formula in cells D2 to I2....no results at all. Let me know. TYSM!