Extracting information from varaible string

[jsantos77]

Hi everyone

I am looking for a formula solution to extract information from a variable string of text. In short I need to segregate 4 pieces of information into 4 different cells

Original String 90896 X SMITH P/P TOMS 500A 5 752315 20
Result desired:
C1 90896
D1 5
E1 752315
F1 20

1- C1 1st number string from left (Can be from 4 to 8 digits long, but it always lead to a space ) I think I got this one!
2- D1 3rd number string from right (can be up to 3 digits)
3- E1 2nd number string from right (can be from 4 to 8 digits long, but will always be between spaces)
4- F1 1st number string from right (can also be up to 3 digits long)

For C1 I have =LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",1))-1) and it seems to be doing the job

What would you sugest for the rest of the cells D1,E1 & F1?

Any suggestions will be greatly appreciated

Thanks
JSantos

 

[jsantos77]

Also come across the following formula that extracts all the number strings but unfortunately displays them all in one string. Is there a way to separate the 4 strings?
=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	90896 X SMITH P/P TOMS 500A 5 752315 20		90896	5	752315	20
Appendix

Cell Formulas
Range	Formula
C1	=--LEFT(A1,FIND(" ",A1)-1)
D1	=--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," "&F1,"")," "&E1,"")," ",REPT(" ",100)),100))
E1	=--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," "&F1,"")," ",REPT(" ",100)),100))
F1	=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[jsantos77]

Thanks Fluff

It does work, however the following 2 strings return an error on cell D[TABLE="width: 324"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]811440 TSY RED PEPPERS 5 90992 5
90983 D SMITH FRESH SWEETCORN P/P 2s 5 90983 5<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
in fact when cell D & F have the same value it returns an error on C

 

[jsantos77]

Not the end of the world, I am miles ahead of where I would have been without your help.
Appreciated Fluff

 

[Fluff]

In that case you will need to supply a representative sample of strings that you want this to work on.

 

[jsantos77]

In that case you will need to supply a representative sample of strings that you want this to work on.

A B C D E F
[TABLE="width: 638"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]811440 TSY RED PEPPERS 5 90992 5[/TD]
[TD][/TD]
[TD]811440[/TD]
[TD]#VALUE![/TD]
[TD]90992[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]811440 TSY RED PEPPERS 5 90992 9[/TD]
[TD][/TD]
[TD]811440[/TD]
[TD]#VALUE![/TD]
[TD]50992[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]811440 TSY RED PEPPERS 6 90992 6[/TD]
[TD][/TD]
[TD]811440[/TD]
[TD]#VALUE![/TD]
[TD]90992[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]811440 TSY RED PEPPERS 7 90992 7[/TD]
[TD][/TD]
[TD]811440[/TD]
[TD]#VALUE![/TD]
[TD]90992[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]811440 TSY RED PEPPERS 5 90992 9[/TD]
[TD][/TD]
[TD]811440[/TD]
[TD]#VALUE![/TD]
[TD]50992[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Is that a representative sample of your data?
What about the other two strings that you have already posted?

 

[jtakw]

Hi,

B1 formula copied down.
C1 formula copied down and across to column E:

Book1
	A	B	C	D	E
1	90896 X SMITH P/P TOMS 500A 5 752315 20	90896	5	752315	20
2	811440 TSY RED PEPPERS 5 90992 5	811440	5	90992	5
3	90983 D SMITH FRESH SWEETCORN P/P 2s 5 90983 5	90983	5	90983	5
4	811440 TSY RED PEPPERS 5 90992 5	811440	5	90992	5
5	811440 TSY RED PEPPERS 5 90992 9	811440	5	90992	9
6	811440 TSY RED PEPPERS 6 90992 6	811440	6	90992	6
7	811440 TSY RED PEPPERS 7 90992 7	811440	7	90992	7
8	811440 TSY RED PEPPERS 5 90992 9	811440	5	90992	9
Sheet618

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1))+0
C1	=MID(RIGHT(SUBSTITUTE($A1," ",REPT(" ",100)),300),COLUMNS($C1:C1)*100-99,100)+0

 

[jsantos77]

Is that a representative sample of your data?
What about the other two strings that you have already posted?

The other 2 strings worked fine with your solution. Only when the 2 values on cell D and F are the same the error occurs. That is why i posted the previous example