Extract a specific number from a line of text in a cell

Copper Contributor

<span;>I need to extract a number from a line of text in cell C3 to a different cell H3. For example text in C3 is: ANGLE, 1-1/2" X 1-1/2" X 1/8" X 60",SP(GALVANIZED)
<span;>I need to extract only the number 60 to another cell H3

7 Replies

Do you happen to have more examples of text with desired numbers to be returned? The goal with any extraction formula is to create something that will work for anything you might encounter.


In H3:

=LET(Parts, TEXTSPLIT(INDEX(TEXTSPLIT(C3, ","), 2), " "), Num, COUNTA(Parts), Last, INDEX(Parts, Num), --SUBSTITUTE(Last, """", ""))

I have lots of similar text in cell c3 but they all have in common the number i need to extract from the last X in the line. So i can extract to cell H3 for a formula in J3 total. It will be regularly used with the pasted data under bill of materials.


What do you want to do with 9'-6" ?






=LET(number,1*TEXTBEFORE(TEXTAFTER(C3,"X ",-1),""""),IFNA(number,""))


No I'm sorry, it really only applied to the column that started with ANGLE, (cells C3:C10
This work great guys I really appreciate your help Hans and Patrick! I learned alot!