return value contained in cell within ""

AJohn01

New Member
Joined
Jun 27, 2012
Messages
10
I have an excel spreadsheet that contains a description column that has cells that contain numbers within ""
eg
Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - "244/12"</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
I need to get a formula that extracts the numbers within the "" or if none return value of NIL

the cells are not consistant in length & some cells do not contain any ""
Thanks in anticipation ...
Anne
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Hello and welcome
Would this work for you?
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - "244/12"</td><td style=";">244/12</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">TRIM(<font color="Teal">A1</font>)," ",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>),"""",""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Correction:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - "244/12"</td><td style=";">244/12</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - 33444</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A1</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A1,"""",""</font>)</font>)>0,SUBSTITUTE(<font color="Red">TRIM(<font color="Green">RIGHT(<font color="Purple">SUBSTITUTE(<font color="Teal">TRIM(<font color="#FF00FF">A1</font>)," ",REPT(<font color="#FF00FF">" ",99</font>)</font>),99</font>)</font>),"""",""</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

AJohn01

New Member
Joined
Jun 27, 2012
Messages
10
Hello and welcome
Would this work for you?
Excel 2010
AB
1Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - "244/12"244/12

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet5

Worksheet Formulas
CellFormula
B1=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"""","")

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>
[=SUBSTITUTE(TRIM(RIGHT(SUBSITIUTE(TRIM(A25),"",REPT("",99)),99)),"""","") gives me an error message #NAME? what can you see wrong, I put spaces between the " " and it still gave the error??
thanks Anne]
 

AJohn01

New Member
Joined
Jun 27, 2012
Messages
10

ADVERTISEMENT

YESSSS the second one with the Len in worked perfectly thanks so very very much.
Anne
 

AJohn01

New Member
Joined
Jun 27, 2012
Messages
10
The only instances where it doesnt work on the sheet is when the data is "4,6" - Disused and it returns the word Disused instead of the 4,6
Guessing its because the comma is inbetween the numbers???
Anne
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

Is the word as a last word in your string?
Could you post some other examples.
 

AJohn01

New Member
Joined
Jun 27, 2012
Messages
10
Hi Robert,
I think when there is a comma seperating it then says Disused if its a / then its in operation...

Signal - Wire Operated-Electrically Lit</SPAN>
Signal Mast @ 244.795 - L/R/SG - 2002 Galv Tube - "244/32"</SPAN>
Signal Mast @ 267.589 - R/R/SG - 1982 Galv Tube - "214/12"</SPAN>
Signal Portal @ 264.660 - Over 2SG+BG - 1930 Ms/lattice - "4, 6" - Disused</SPAN>
Signal Portal @ 264.833 - Over SG+BG - 1930 Ms/lattice - "9, 11" - Disused</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Sheet1

*AB
1Signal - Wire Operated-Electrically Lit*
2Signal Mast @ 244.795 - L/R/SG - 2002 Galv Tube - "244/32"244/32
3Signal Mast @ 267.589 - R/R/SG - 1982 Galv Tube - "214/12"214/12
4Signal Portal @ 264.660 - Over 2SG+BG - 1930 Ms/lattice - "4, 6" - Disused4, 6
5Signal Portal @ 264.833 - Over SG+BG - 1930 Ms/lattice - "9, 11" - Disused9, 11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:395px;"><col style="width:146px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=IF(COUNTIF(A1,"*""*")=0,"",LEFT(MID(SUBSTITUTE(A1,"""","~",2),FIND("""",SUBSTITUTE(A1,"""","~",2))+1,40),FIND("~",MID(SUBSTITUTE(A1,"""","~",2),FIND("""",SUBSTITUTE(A1,"""","~",2))+2,40))))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,327
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top