Forum Discussion
Separate Integer or Decimal from Text
Hi
I have values like this:
BL, JZ5, CC6.3
So Text combined with an integer or Text combined with a decimal value.
I would like the data to split into Col A and Col B
| Data | Col A | Col B |
| BL | BL | |
| JZ5 | JZ | 5 |
| CC6.3 | CC | 6.3 |
Could anyone help with a power query solution to splilt the text from the number?
Just not having any luck with my power query trials.
Thanks
Following will be a simple solution based on test cases presented by you.
For selecting Text = Text.Select([Data], {"A".."Z","a".."z"}) For selecting Number = Text.Select([Data], {"0".."9","."})
4 Replies
Following will be a simple solution based on test cases presented by you.
For selecting Text = Text.Select([Data], {"A".."Z","a".."z"}) For selecting Number = Text.Select([Data], {"0".."9","."})Or split by character transition
= Table.SplitColumn(
Source,
"Data",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}&{"."}, c), {"0".."9"}),
{"Col A", "Col B"})Stéphane
if the first two characters are always letters followed by a number or not, then you can split the columns after the second character. "Add Column", "Extract", "Length" for example?
Sorry - I should have mentioned that the text might be 2-3-4 characters, so none of those solutions work.
