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

 

[jtakw]

Same as my formulas in Post # 9, just changed the Columns references to match your requirement C, D, E, F

Again C1 formula copied Down.
D1 formula copied Down And Across to Column F

Book1
	A	B	C	D	E	F
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
C1	=LEFT(A1,FIND(" ",A1))+0
D1	=MID(RIGHT(SUBSTITUTE($A1," ",REPT(" ",100)),300),COLUMNS($D1:D1)*100-99,100)+0

 

[jsantos77]

Thanks jtakw, tested and works a treat

 

[jtakw]

You're welcome, thanks for the feedback.