Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
Received the folowing error message:
"The search Text provided to function 'FIND' could not be found in the given text."
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
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?
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |