Forum Discussion

Steve-TESTRITE's avatar
Steve-TESTRITE
Copper Contributor
Nov 18, 2024

formula to extract number value before a specific criteria

Hello,

I'm trying to extract first batch of numbers from the string below so it'll be 300, can anyone help?

A formula to call out number value before text value "Wh"

Jackery 300w portable power station 24 hour run time.

 

thanks

excel

5 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 19, 2024
    =VALUE( TAKE( TEXTSPLIT( TEXTBEFORE( A1, "Wh " ), " " ),, -1 ) )

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Nov 19, 2024

    Try the following formula-

    =@LET(x,--TEXTSPLIT(SUBSTITUTE(A1,"w ", " "),," "),FILTER(x,ISNUMBER(x)))

     

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Nov 19, 2024

    A formula to call out number value before text value "Wh"

    Jackery 300w

    "Wh" or "w"

    if "w",try REGEXEXTRACT Function

    https://support.microsoft.com/en-us/office/regexextract-function-4b96c140-9205-4b6e-9fbe-6aa9e783ff57

     

     

Resources