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 ...
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

Tough. How about this

 

1 do a replace values and replace MI with something like a pipe |

2 then split the column at the pipe

3 then split on the fist space from the left hand side of the first column

4 then convert to a number

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

    Thanks Mike. What if there's other numbers mixed within the description and you only want to extract the numbers associated to MI?

     

    eg. 

    1243 Factory 12 03 OH Expense OHIO Plant IS 9.12 MI

     or

    12A Factory OH 9.12 MI Expense OHIO Plant IS 64