Forum Discussion

Yomnaehab's avatar
Yomnaehab
Copper Contributor
Jun 07, 2023
Solved

Extract characters from string

Hello. in the screenshot i have column with data and i want to extract the number before MW. the character length is not constant and location of the numbers as well. would appreciate any help usin...
BI & Data Analysis
excel
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    Jun 07, 2023

    Hi Yomnaehab 

     

    Here you go with the updated file version now returning zero

     

    Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget to mark it as Official/Best Answer to help the other members find it more

Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 07, 2023

Hi Yomnaehab 

 

If there's always a <space> before the number preceeding MW - as in your picture - the following custom M function appears to do it:

 

// ExtractNumBeforeDelim
(string as text, delim as text) as nullable number =>
let
    Source = string,
    BeforeDelim = Text.Trim(Text.BeforeDelimiter(Source,delim)),
    StrEndsAtLastDigit = Text.Middle(BeforeDelim, 0, Text.PositionOfAny(BeforeDelim,{"0".."9"},Occurrence.Last)+1),
    AfterLastSpace = Text.AfterDelimiter(StrEndsAtLastDigit, " ", {0,RelativePosition.FromEnd}),
    TriedNumber = try Number.FromText(AfterLastSpace) otherwise 0
in
    if Text.Contains(Source, delim) then TriedNumber else 0

Resources