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?

 

[JEC]

True, I wasn't aware of that possibility. I changed the formula above

 

[Joe4]

True, I wasn't aware of that possibility. I changed the formula above

OK, that corrects that issue, but still has a problem with their latest condition, that some might not end in numbers, and those should just return a blank (nothing).
(See posts 5 and 7 for those details).

 

[OrderTester1]

Actually The =IF(ISERROR(MID(A1,LEN(A1)-1,1)+0),"",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"x",REPT(" ",100)),"X",REPT(" ",100)),"'",""),100))) started to work and a bunch of cells were blank, but then others were still putting in the text. There are over 11,000 lines and each cell is different with different items. In the example below, you'll see that the cells are not blank, except for one. And this formula =SUBSTITUTE(TRIM(MID(A2,SEARCH(CHAR(39),A2&CHAR(39))-3,99)),CHAR(39),"") didn't work for me because it made the # of Feet column a 0 instead of blank, which won't work.

Description	# of Feet
SANITARY TRI-CLMP X MP 2.5X2"	2"
SANITARY TRI-CLMP X MP 3"	MP 3"
SANITARY TRI-CLMP X MP 3"X2"	2"
SANITARY TRI-CLMP X MP 4"	MP 4"
SANITARY TRI-CLAMP CAP 1&1-1/2
SANITARY TRI-CLAMP END CAP 2"	SANITARY TRI-CLAMP END CAP 2"
SANITARY TRI-CLMP END CAP 2.5"	SANITARY TRI-CLMP END CAP 2.5"
SANITARY TRI-CLAMP END CAP 3"	SANITARY TRI-CLAMP END CAP 3"
SANITARY TRI-CLAMP END CAP 4"	SANITARY TRI-CLAMP END CAP 4"
SANITARY TRI-CLAMP FTG 1"	SANITARY TRI-CLAMP FTG 1"
SANITARY TRI CLAMP FTG 1 1/2"	SANITARY TRI CLAMP FTG 1 1/2"

 

[Joe4]

You are welcome.
Glad we were able to help!

Maybe someone will come up with a shorter/simpler formula, but ti works, never-the-less!

 

[OrderTester1]

So basically the cells that I'm trying to extract the footage from are descriptions so they'll end in footage (i.e. 25, 50, 100, 500, etc. or 25', 50', 100', 500', etc., but I just noticed that some have the footage with either a ' or no ', but then also a space and TBG - i.e. 100' TBG, 100 TBG). Then some cells end in a letter, some end in a size such as 1/2 or 1/2" so they could end in ", as well. I basically just need the footage only extracted, which is at the end of the cell, but I need the ' and the <space>TBG removed from that number and then any other cell without footage, I need to be just blank.

 

[OrderTester1]

Actually, I edited my reply (see above) because it only worked for some of the cells (Where it would put the footage or blank, but then other cells still had text, instead of being blank). So basically the cells that I'm trying to extract the footage from are descriptions so they'll end in footage (i.e. 25, 50, 100, 500, etc. or 25', 50', 100', 500', etc., but I just noticed that some have the footage with either a ' or no ', but then also a space and TBG - i.e. 100' TBG, 100 TBG). Then some cells end in a letter, some end in a size such as 1/2 or 1/2" so they could end in ", as well. I basically just need the footage only extracted, which is at the end of the cell, but I need the ' and the <space>TBG removed from that number and then any other cell without footage, I need to be just blank.

 

[Joe4]

This is seeming to become a Catch-22 (every time we come up with an answer that works, the question changes)!
I don't know if I can come up with a solution to account for all your different scenarios.
But before I even try, let's try to put an end to getting a little bit on important information at at time, and let's try to get everything out there up front.

Please provide a sample that includes examples of ALL your different scenarios, and the expected results for each one.
So please take the time to carefully look at your data, and identify all these different situations that need to be accounted for.

 

[OrderTester1]

Thanks for your help, but never mind - it's probably never going to have an answer becuase there are way too many scenerios. I just reviewed ALL 11,000 line items and manually deleted those that had text in it, when it should've been blank and then add in the footage for those that actually did have verbiage after it. There's probably like , AT LEAST, 3,000 different scenarios. But thanks, again for your help.

 

[Joe4]

Thanks for your help, but never mind - it's probably never going to have an answer becuase there are way too many scenerios. I just reviewed ALL 11,000 line items and manually deleted those that had text in it, when it should've been blank and then add in the footage for those that actually did have verbiage after it. There's probably like , AT LEAST, 3,000 different scenarios. But thanks, again for your help.

You are welcome.

Yes, it can be like chasing your tail when there are no standards and data can come across in any well-defined manner.
In those instances, what I usually like to try to do is come up with an automated solution that handles at least 90% of the scenarios.
If I can do that, then I can just manually take care of the exceptions.

 

[OrderTester1]

Yes, that's what I did. I used your formula and then just went in and manually corrected those other cells, but the formula helped a great deal and I appreciate the support on this. Thanks, again.