Forum Discussion
How to extract specific number of characters in Power Query
Hello, do you know how to extract specific number of characters from text?
The main rule is: if text contains CC then extract only 6 characters (as you can see on the picture) and if text contains RECHARGE then extract specific18 characters.
Jackie003, you can try this. But I can only guess that you want to extract 6 or 18 characters after 1st space (you haven't mentioned this).
Add this as Custom Column and replace [Column1] with your column reference
[ a = Text.Trim([Column1]), b = Text.PositionOf(a, " ")+1, c = if Text.StartsWith(a, "CC") then Text.Range(a, b, 6) else if Text.StartsWith(a, "RECHARGE") then Text.Range(a, b, 18) else null ][c]
6 Replies
- Khushidesai0109
Skilled Sharer
if Text.Contains([Text], "CC") then Text.Middle([Text], Text.PositionOf([Text], "CC"), 6)
else if Text.Contains([Text], "RECHARGE") then Text.Middle([Text], Text.PositionOf([Text], "RECHARGE"), 18)
else [Text])
try this M language in power query add custom column will help you
if this help you please give thumbs up and accept it as solution!! - dufoq3
Community Champion
Hi, this will do the job for your sample data: use space as start and also end delimiter
In line 5 your sample contains CC as well as RECHARGE. What do you want to do with this row? If you want just extract A23456, you can use instructions above. I'm asking because you mentioned CONTAINS, not STARTS WITH.
Sorry, Of course I meant Starts with 🙂
Sorry, but I received first characters from Column 1 (6 or 18) and for example the output is: "CC. 50" instead of "505050"
- dufoq3
Jackie003, you can try this. But I can only guess that you want to extract 6 or 18 characters after 1st space (you haven't mentioned this).
Add this as Custom Column and replace [Column1] with your column reference
[ a = Text.Trim([Column1]), b = Text.PositionOf(a, " ")+1, c = if Text.StartsWith(a, "CC") then Text.Range(a, b, 6) else if Text.StartsWith(a, "RECHARGE") then Text.Range(a, b, 18) else null ][c] - dufoq3
If you use my code with your sample data - it will work 😉
