return value contained in cell within ""

[AJohn01]

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

 

[Robert Mika]

Hello and welcome
Would this work for you?

Excel 2010
	A	B
1	Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - "244/12"	244/12
Sheet5

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"""","")

 

[Robert Mika]

Correction:

Excel 2010
	A	B
1	Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - "244/12"	244/12
2	Signal Mast @ 243.968 - R/R/ML/SG - 2002 Galv Tube - 33444
Sheet5

Cell Formulas
Range	Formula
B1	=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))>0,SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"""",""),"")

 

[AJohn01]

Hello and welcome
Would this work for you?
Excel 2010

	A	B
1	Signal 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 Cell Formula 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]

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

 

[AJohn01]

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]

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

 

[AJohn01]

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]

Sheet1

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

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

Spreadsheet Formulas

Cell Formula 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

 

[AJohn01]

Thanks so very much... you are a treasure.
Anne