Forum Discussion

mdrammeh's avatar
mdrammeh
Icon for Helper III rankHelper III
10 years ago

How to extract number from text

I am trying to figure out how to extract numbers numbers from a text.   For example, if I want to extract the number of miles (MI) from this description:   Factory OH Expense ABCD1315 14.0 MI ...
v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

mdrammeh

 

You can also do it by creating a column with following formula.

Num =
TRIM (
    RIGHT (
        SUBSTITUTE (
            LEFT ( Table1[Texts], FIND ( "MI", Table1[Texts] ) - 2 ),
            " ",
            REPT ( " ", 100 )
        ),
        100
    )
)

 

Best Regards,

Herbert

 

  • mdrammeh's avatar
    mdrammeh
    Icon for Helper III rankHelper III
    10 years ago

    Thanks for the feedback Herbert and your response. There are instances where the column number of miles is located over 30 characters from the right so in that case, could I use this formula to extract just the number? Or would it change everytime the position of the number of miles change? 

  • mdrammeh's avatar
    mdrammeh
    Icon for Helper III rankHelper III
    10 years ago

    Received the folowing error message:

     

    "The search Text provided to function 'FIND' could not be found in the given text."

    • v-haibl-msft's avatar
      v-haibl-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      mdrammeh

       

      Do some text stings in your data not have “MI” in them? The error message will return if it cannot find “MI” in the column values.

       

      Best Regards,

      Herbert