Extract characters from string

[Yomnaehab]

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. 

[#2]

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

[#3]

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

[#4]

I want it to return 0 when there's no MW

[#5 · Accepted solution]

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

[#6]

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

[#7]

this worked thank you so much!