SOLVED

Extract characters from string

Copper Contributor

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. 

mw.png

6 Replies
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

hi @Yomnaehab

 

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

 

FarazShaikh_0-1686123939021.png

 

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

 

FarazShaikh_1-1686124113106.png

 

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

I want it to return 0 when there's no MW
best response confirmed by Yomnaehab (Copper Contributor)
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

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
this worked thank you so much!
1 best response

Accepted Solutions
best response confirmed by Yomnaehab (Copper Contributor)
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

View solution in original post