Forum Discussion

Yomnaehab's avatar
Yomnaehab
Copper Contributor
Jun 07, 2023

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 using a formula or power query. 

  • 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

    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
  • What about the data when there is no MW written next to it ? could you please attach sample file to work on this query.
    Regards, Faraz Shaikh
    • Yomnaehab's avatar
      Yomnaehab
      Copper Contributor
      I want it to return 0 when there's no MW
      • 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

  • hi Yomnaehab

     

    Here is the solution file you might need to try with your dataset

     

     

    Steps:

    1. remove the spaces between MW

    2. Extract Only MW

    3. Merege source table to MW

     

    Here are the Query Dependencies that might be helpful to understand

     

     

    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

Resources