Hello,
thanks to everyone who participated! “Jamás se me habrĂa ocurrido…” or “I look and try to understand others’ solutions to learn” are pretty good quotes in a workout thread. Love it!
I am not trying to add a better solution, just two variants plus one alternative approach.
I very much like the approach with Splitter.SplitTextByCharacterTransition({“0”…“9”}, {" "} but it is not doable just by clicking. The task was to create a separate column with Part Numbers, so we can start off by a right mouse on Summary and selecting Duplicate Column:
Then select on the Split Column dropdown By Digit to Non-Digit:
In the formula bar then replace
= Table.SplitColumn(#“Duplicated Column”, “Summary - Copy”, Splitter.SplitTextByCharacterTransition({“0”…“9”}, (c) => not List.Contains({“0”…“9”}, c)), {“Summary - Copy.1”, “Summary - Copy.2”, “Summary - Copy.3”, “Summary - Copy.4”, “Summary - Copy.5”})
with
= Table.SplitColumn(#“Duplicated Column”, “Summary - Copy”, Splitter.SplitTextByCharacterTransition({“0”…“9”}, {" "}), {“Part Number”} )
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Summary", "Summary - Copy"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Summary - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "}), {"Part Number"} )
in
#"Split Column by Character Transition"
@Rajesh has a nice one-liner with Splitter.SplitTextByCharacterTransition({“0”…“9”}, {" "}
@lillie72 had an interesting approach with removing {“a”…“z”}. You could select on the Add Column tab Custom Column:
Call the New Column Name “Part Number” and add this formula:
= Text.Remove([Summary],{“a”…“z”})
That’s good for the given data, but we do have now trailing blanks which we can remove by Trim. Right mouse Part Number and select Transform > Trim
Done:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Added Part Number" = Table.AddColumn(Source, "Part Number", each Text.Remove([Summary],{"a".."z"})),
#"Trimmed Text" = Table.TransformColumns(#"Added Part Number",{{"Part Number", Text.Trim, type text}})
in
#"Trimmed Text"
Talking about Trim, check out the slim Text.TrimEnd solution from @RickdeGroot. It has the benefit that it works also if the portion after the part number does include capital letters!
Finally let me add one general alternative if you have more than one criterion for extracting or splitting.
Add a Custom Column as above and add an if statement checking which case is contained in the Summary column. In our case if the string starts with a “number” we can extract the text before the first blank and otherwise the text before the second blank:
= Table.AddColumn(Source, “Part Number”, each if List.Contains({“0”…“9”}, Text.Start([Summary],1) ) then Text.BeforeDelimiter([Summary], " ") else Text.BeforeDelimiter([Summary], " ", 1))
With the given data you have other alternatives, but this approach with different cases can be helpful for complex extraction or splitting tasks.
Try it out and make sure to check the other approaches above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! I have taken this now out so that it’s easier to compare solutions.
