Extract characters from string

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

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