Extracting data from a description

[BigGee]

Hello,
I am trying to extract 2 parts from a text field into separate columns. I have been trying to use the MID function and even though I have had it work in a previous similar instance I am having difficulty I think because of the fact that my model can have a different number of characters
Could someone assist please?

 

[Joe4]

Can you show us your expected results?
Also, are you using 365 or 2019 to do this?
365 has some really cool new text functions that may make this a bit easier.

 

[BigGee]

Hello Joe
I am using 365
I want eventually to show in column B B2 "JUBILEE-1" B3 "SPECIAL-5" and B4 "CARNIVAL-10" and
in column C C2 "RED-XL" C3 "BLUE-M" C4 "GREEN-L"

In an ideal world I would like to also know how to have the Colour and the size eg. Green and L (for large) separate.

 

[BigGee]

I have been able to this using TEXTSPLIT function following Joe's suggestion.
Would still like to know if there is a different/better way though
Especially as I have found that some cells have a space in that then messes it all up.

 

[Joe4]

I have been able to this using TEXTSPLIT function follwoing Joe's suggestion.
Would still like to know if there is a different/better way though

Are you saying you got it working now?
TEXTSPLIT is, most likely, the preferred/best way of doing it.
Is there some reason you think otherwise?

If you post the formulas you used, we will see if there may be a way to shorten them (if you have made them more complicated than they need to be).

 

[BigGee]

Hi Joe,
Yes I used the TEXTSPLIT function - however where there is a "difference" in the format (per my edit to my last message) it throws the columns out.

in this instance I would liked the PRESENT-5 and SPECIAL into column E and G5 and H5 to be in F5 and G5 respectively as the previous rows.

I used =mid (a2,find("-",A2)-1,99) originally but I couldn't get it to work to show the whole of the model number because of the difference in the number of characters in the Model

 

[Joe4]

Like this?

Here is the formulas I used:

B2:

=TEXTBEFORE(A2," ",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)

C2:

=TEXTBEFORE(TRIM(TEXTAFTER(A2,B2))," ",1)

D2:

=TRIM(TEXTAFTER(A2,C2,1))

 

[Cubist]

One more option:

Book1
	A	B	C	D	E
1
2	JUB-1 15X15 RED-XL	JUB-1	15X15	RED	XL
3	SPECS-5 10X15 BLUE-M	SPECS-5	10X15	BLUE	M
4	CARNIVIAL-10 15X15 GREEN-L	CARNIVIAL-10	15X15	GREEN	L
5	PRSENT-5 SPECIAL 20X12 YELLOW-S	PRSENT-5 SPECIAL	20X12	YELLOW	S
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=TEXTBEFORE(A2," ",-2)
C2:E5	C2	=TEXTSPLIT(TEXTAFTER(A2," ",-2),{" ","-"})
Dynamic array formulas.

 

[BigGee]

Thank you Joe, you have been so helpful.
I am sorry to be a pain but I have run into another issue in that I have found some occassions (only some) where there is an EXTRA variant on the end.
This throws everything out further back in the process.
Is there a way to amend the formula so it just trims forward each time?

Below hopefully shows my issue

 

[Joe4]

I have a feeling this may not be the last of the exceptions.
Before we go any further, we need you to:

1. First identify ALL possible structures to these values
2. Explain (in plain English) the "rules" for knowing exactly where to split each value