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

Copper Contributor

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

<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

# Re: Extract a specific number from a line of text in a cell

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.

# Re: Extract a specific number from a line of text in a cell

In H3:

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

# Re: Extract a specific number from a line of text in a cell

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.

# Re: Extract a specific number from a line of text in a cell

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

# Re: Extract a specific number from a line of text in a cell

Revised:

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

# Re: Extract a specific number from a line of text in a cell

No I'm sorry, it really only applied to the column that started with ANGLE, (cells C3:C10

# Re: Extract a specific number from a line of text in a cell

This work great guys I really appreciate your help Hans and Patrick! I learned alot!