Forum Discussion
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
- LorenzoSilver 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- YomnaehabCopper ContributorI 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