Extract Certain Numbers from a Cell

[OrderTester1]

I need to extract footage from a cell that contains numbers and text (i.e. T340 BLK BRD PDM STM 3/4"x100) I need to extract the 100 because this is the footage. Sometimes, it'll just be the footage like 100, 50, 25 and then some of the cells have the footage symbol so it'll be 100', 50', 25'. Someone else created the spreadsheet and it's inconsistent. I would've had the footage symbol after all footage. Also, the footage is usually at the end of the string in the cell. In any case, I want to extract the footage number from the cell containing text and numbers, to another cell. I had created a formula or maybe used Power Query, but it would still take some of the other numbers and text out and combine it with the footage (so the cell wasn't just the footage number). Some sells would just be the footage like 100, 50, 25, etc.,, but then other cells would contain parts of the text or or other numbers (i.e. 3/4"X100). Thus, I had to review over 11,000 cells and correct the footage cells so that it only had the footage in a number format without anything else (i.e. 100, 50, 25). Do you know of a formula to do this?

 

[Joe4]

Are all the following statements about your data ALWAYS true?
1. You always want the last number
2. The only thing that may ever exist after this last number is the footage symbol
3. There will always be a "X" or "x" just before this last number

If all three of these things are not always true, please post a bunch of examples showing the different formats you are working with.

 

[OrderTester1]

Yes, ALWAYS true.

 

[Joe4]

For a value in cell A1, see if this formula works:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"x",REPT(" ",100)),"X",REPT(" ",100)),"'",""),100))

 

[OrderTester1]

This is working, but some of the cells do not have footage so those cells should remain blank. However, when I copy the formula down the column, it is just copying over the same text from those cells. Is there an addition to this formula to make the cells without a footage just be blank?

 

[Joe4]

This is working, but some of the cells do not have footage so those cells should remain blank.

What exactly might these other cells look like?
Do they follow any sort of certain pattern that we can use to easily identify them?

However, when I copy the formula down the column, it is just copying over the same text from those cells.

It sounds like you have your calculation mode set to "Manual".
If you press F9, do they all calculate correctly then?
If so, I would recommend going in to the Properties/Settings on the workbook and setting the Calculation mode to "Automatic".

 

[OrderTester1]

It is set to automatica and F9 doesn't do anything. Here is an example below - this is usiing the =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"x",REPT(" ",100)),"X",REPT(" ",100)),"'",""),100)) formula in the # of Feet cells. The ones that don't have footage and just text I want to be blank in the # of feet column, but it's putting the text.

Item Description	# of feet
NYLON NAT 4MMX6MMX500'	500
NYLON NAT 6MMX8MMX100'	100
NYLON NAT 6MMX8MMX500'	500
NYLON NAT 8MMX10MMX100'	100
NYLON NAT 8MMX10MMX500'	500
NYLON NAT 10MMX12MMX100'	100
NYLON NAT 10MMX12MMX500'	500
1 1/2 x 100' JAFRIB RED	100 JAFRIB RED
1 1/2 x 25' JAFRIB RED	25 JAFRIB RED
1 1/2 x 50' JAFRIB RED	50 JAFRIB RED
1 1/2 x 100' JAFRIB YELLOW	100 JAFRIB YELLOW
1 1/2 x 25' JAFRIB YELLOW	25 JAFRIB YELLOW
1 1/2 x 50' JAFRIB YELLOW	50 JAFRIB YELLOW
1 3/4 x 100' JAFRIB RED	100 JAFRIB RED
1 3/4 x 25' JAFRIB RED	25 JAFRIB RED
1 3/4 x 50' JAFRIB RED	50 JAFRIB RED
1 3/4 x 100' JAFRIB YELLOW	100 JAFRIB YELLOW
1 3/4 x 25' JAFRIB YELLOW	25 JAFRIB YELLOW
1 3/4 x 50' JAFRIB YELLOW	50 JAFRIB YELLOW
2 1/2 x 100' JAFRIB RED	100 JAFRIB RED

 

[JEC]

How about

=SUBSTITUTE(TRIM(MID(A2,SEARCH(CHAR(39),A2&CHAR(39))-3,99)),CHAR(39),"")

 

[Joe4]

Try this:

=IF(ISERROR(MID(A1,LEN(A1)-1,1)+0),"",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"x",REPT(" ",100)),"X",REPT(" ",100)),"'",""),100)))

 

[Joe4]

How about

=SUBSTITUTE(TRIM(MID(A2,SEARCH(CHAR(39),A2)-3,99)),CHAR(39),"")

JEC, that will returns errors for the ones that do not end with a single-quote.
From the first line of his original question, it appears that some entries do NOT have a single-quote on the end, and just end with the number.