Jun 07 2023 12:01 AM
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.
Jun 07 2023 12:28 AM
Jun 07 2023 12:50 AM
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
Jun 07 2023 01:22 AM
SolutionHi @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
Jun 07 2023 02:20 AM
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
Jun 07 2023 01:22 AM
SolutionHi @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