Power Query Function similar to MID - Extracting number from a text string

[jonastiger]

Hi
I would like to get some help with the following issue

[Data] Column

Abcde 8 Efghijk
Abcde 16 Efghijk
Abcde 14 Efghijk
Abcde 4 Efghijk
Abcde 3 Efghijk
Abcde 8 Efghijk

I want to extract numbers from the text string.
I tried Text.Middle, Text.Range and other options but I can´t replicate my excel formula:
=IFERROR(MID([Data],0)),6,3)*7,0)

What is the coorected function to use?

Thank you all in advance.
JT

 

[John Jairo V]

hi, @jonastiger!

You could try this M formula:

=try 7 * Number.FromText(Text.Split([Data], " "){1}) otherwise 0

Blessings!

 

[GraH - Guido]

Why not simply use some UI functions:
1. split between delimiters
2. convert to number
3. multiply
(4. replace errors)

Code being

let
  Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
  #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Data", each Text.BetweenDelimiters(_, " ", " "), type text}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Between Delimiters",{{"Data", Int64.Type}}),
  #"Multiplied Column" = Table.TransformColumns(#"Changed Type1", {{"Data", each _ * 7, type number}})
in
  #"Multiplied Column"

or...
= try Number.FromText(Text.BetweenDelimiters([Data], " ", " "))*7 otherwise 0

 

[jonastiger]

HI John Jaro & Guido
Sorry for the late response.
Thank you very much for your help. Both options works perfectly and solved my problem.
Bless you two.
JT