# Extracting information from varaible string

#### jsantos77

##### New Member
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### jsantos77

##### New Member
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)

#### jsantos77

##### New Member
Thanks Fluff

It does work, however the following 2 strings return an error on cell D
 811440 TSY RED PEPPERS 5 90992 5 90983 D SMITH FRESH SWEETCORN P/P 2s 5 90983 5

<tbody>
</tbody>
in fact when cell D & F have the same value it returns an error on C

Last edited:

#### jsantos77

##### New Member

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

#### Fluff

##### MrExcel MVP, Moderator
In that case you will need to supply a representative sample of strings that you want this to work on.

#### jsantos77

##### New Member

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
 811440 TSY RED PEPPERS 5 90992 5 811440 #VALUE! 90992 5 811440 TSY RED PEPPERS 5 90992 9 811440 #VALUE! 50992 9 811440 TSY RED PEPPERS 6 90992 6 811440 #VALUE! 90992 6 811440 TSY RED PEPPERS 7 90992 7 811440 #VALUE! 90992 7 811440 TSY RED PEPPERS 5 90992 9 811440 #VALUE! 50992 9

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

#### Fluff

##### MrExcel MVP, Moderator
Is that a representative sample of your data?

#### jtakw

##### Well-known Member
Hi,

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

#### jsantos77

##### New Member
Is that a representative sample of your data?

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

Replies
10
Views
108
Replies
7
Views
114
Replies
9
Views
57
Replies
10
Views
108
Replies
3
Views
66