How may I extract only the numbers from a cell?

Enrico Trovato

I need to extract 16 and 9.

As you can see the number characthers may change (I may have 1-2 digits) but always at the beginning.

Thanks

Brett Wyrick

Hey @Enrico Trovato, welcome to the community!

Couple of questions for you:

1. Does the cell need to be split at that number (i.e. "16" goes in Column1, "A STD RB" goes in Column2)? Or is it okay if it stays in that cell?
2. Are there any more numbers besides 16 and 9?

If it's okay for the "16" or "9" to stay in that cell and the only numbers are 16 and 9, then this formula works to get either 16 or 9 in the column.

(replace "ColumnName" with your column's name):

=IF(CONTAINS("16", ColumnName@row), 16, IF(CONTAINS("9", ColumnName@row), 9, ""))

Otherwise, if you have more numbers besides 16 and 9 and you'd like to extract them, you do something like:

=IFERROR(
    IF(
        AND(VALUE(LEFT(ColumnName@row, 2)) > 0), LEFT(ColumnName@row, 2)), 
        
    IFERROR(
        IF(
            AND(VALUE(LEFT(ColumnName@row, 1)) > 0, ISTEXT(MID(ColumnName@row, 2, 1))), 
                VALUE(LEFT(ColumnName@row, 1)), 
                    ""), 
""))

This basically says "If the value of the first two characters are greater than 0, show me that value. But if there's an error (i.e. "9A" throws an error because it's not a number), then show me the value of the first character.

This will only work with two digits. You'd have to expand this formula to work with more.

You can copy/paste the formulas from here in the cells.

If this answered your question, please select "Yes" below - it helps others in the community find solutions they are looking for and random Googlers out there 👀

Brett Wyrick

Hey @Enrico Trovato, welcome to the community!

Couple of questions for you:

1. Does the cell need to be split at that number (i.e. "16" goes in Column1, "A STD RB" goes in Column2)? Or is it okay if it stays in that cell?
2. Are there any more numbers besides 16 and 9?

If it's okay for the "16" or "9" to stay in that cell and the only numbers are 16 and 9, then this formula works to get either 16 or 9 in the column.

(replace "ColumnName" with your column's name):

=IF(CONTAINS("16", ColumnName@row), 16, IF(CONTAINS("9", ColumnName@row), 9, ""))

Otherwise, if you have more numbers besides 16 and 9 and you'd like to extract them, you do something like:

=IFERROR(
    IF(
        AND(VALUE(LEFT(ColumnName@row, 2)) > 0), LEFT(ColumnName@row, 2)), 
        
    IFERROR(
        IF(
            AND(VALUE(LEFT(ColumnName@row, 1)) > 0, ISTEXT(MID(ColumnName@row, 2, 1))), 
                VALUE(LEFT(ColumnName@row, 1)), 
                    ""), 
""))

This basically says "If the value of the first two characters are greater than 0, show me that value. But if there's an error (i.e. "9A" throws an error because it's not a number), then show me the value of the first character.

This will only work with two digits. You'd have to expand this formula to work with more.

You can copy/paste the formulas from here in the cells.

If this answered your question, please select "Yes" below - it helps others in the community find solutions they are looking for and random Googlers out there 👀

Enrico Trovato

@Brett Wyrick works perfectly!!

Thanks a lot per your precious help