cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdrammeh Member
Member

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 Florida GAME PT 1

 

And

 

Factory OH Expense OHIO Plant IS 9.12 MI

 

Or anytime the position changes?

 

6 REPLIES 6
Highlighted
Super User
Super User

Re: How to extract number from text

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
v-haibl-msft Super Contributor
Super Contributor

Re: How to extract number from text

@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
    )
)

How to extract number from text_1.jpg

 

Best Regards,

Herbert

 

mdrammeh Member
Member

Re: How to extract number from text

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 Member
Member

Re: How to extract number from text

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 
mdrammeh Member
Member

Re: How to extract number from text

Received the folowing error message:

 

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

v-haibl-msft Super Contributor
Super Contributor

Re: How to extract number from text

@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.

How to extract number from text_1.jpg

 

Best Regards,

Herbert

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 379 members 3,266 guests
Please welcome our newest community members: