Extracting information from varaible string

jsantos77

New Member
Joined
Sep 18, 2008
Messages
16
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
 

Some videos you may like

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
Joined
Sep 18, 2008
Messages
16
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">90896 X SMITH P/P TOMS 500A 5 752315 20</td><td style="text-align: right;;"></td><td style="text-align: right;;">90896</td><td style="text-align: right;;">5</td><td style="text-align: right;;">752315</td><td style="text-align: right;;">20</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Appendix</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=--LEFT(<font color="Blue">A1,FIND(<font color="Red">" ",A1</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=--TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">A1," "&F1,""</font>)," "&E1,""</font>)," ",REPT(<font color="Purple">" ",100</font>)</font>),100</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=--TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A1," "&F1,""</font>)," ",REPT(<font color="Purple">" ",100</font>)</font>),100</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F1</th><td style="text-align:left">=--TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A1," ",REPT(<font color="Purple">" ",100</font>)</font>),100</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

jsantos77

New Member
Joined
Sep 18, 2008
Messages
16
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<strike></strike>

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

jsantos77

New Member
Joined
Sep 18, 2008
Messages
16

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
In that case you will need to supply a representative sample of strings that you want this to work on.
 

jsantos77

New Member
Joined
Sep 18, 2008
Messages
16

ADVERTISEMENT

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 5811440#VALUE!909925
811440 TSY RED PEPPERS 5 90992 9811440#VALUE!509929
811440 TSY RED PEPPERS 6 90992 6811440#VALUE!909926
811440 TSY RED PEPPERS 7 90992 7811440#VALUE!909927
811440 TSY RED PEPPERS 5 90992 9811440#VALUE!509929

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Is that a representative sample of your data?
What about the other two strings that you have already posted?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">90896 X SMITH P/P TOMS 500A 5 752315 20</td><td style="text-align: right;;">90896</td><td style="text-align: right;;">5</td><td style="text-align: right;;">752315</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">811440 TSY RED PEPPERS 5 90992 5</td><td style="text-align: right;;">811440</td><td style="text-align: right;;">5</td><td style="text-align: right;;">90992</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">90983 D SMITH FRESH SWEETCORN P/P 2s 5 90983 5</td><td style="text-align: right;;">90983</td><td style="text-align: right;;">5</td><td style="text-align: right;;">90983</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">811440 TSY RED PEPPERS 5 90992 5</td><td style="text-align: right;;">811440</td><td style="text-align: right;;">5</td><td style="text-align: right;;">90992</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">811440 TSY RED PEPPERS 5 90992 9</td><td style="text-align: right;;">811440</td><td style="text-align: right;;">5</td><td style="text-align: right;;">90992</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">811440 TSY RED PEPPERS 6 90992 6</td><td style="text-align: right;;">811440</td><td style="text-align: right;;">6</td><td style="text-align: right;;">90992</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">811440 TSY RED PEPPERS 7 90992 7</td><td style="text-align: right;;">811440</td><td style="text-align: right;;">7</td><td style="text-align: right;;">90992</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">811440 TSY RED PEPPERS 5 90992 9</td><td style="text-align: right;;">811440</td><td style="text-align: right;;">5</td><td style="text-align: right;;">90992</td><td style="text-align: right;;">9</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet618</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">" ",A1</font>)</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=MID(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">$A1," ",REPT(<font color="Purple">" ",100</font>)</font>),300</font>),COLUMNS(<font color="Red">$C1:C1</font>)*100-99,100</font>)+0</td></tr></tbody></table></td></tr></table><br />
 

jsantos77

New Member
Joined
Sep 18, 2008
Messages
16
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,416
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top