How to extract specific number of characters in Power Query

[Jackie003]

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]

Sorry, but I received first characters from Column 1 (6 or 18) and for example the output is: "CC. 50" instead of "505050"

[dufoq3 · Accepted solution]

@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 😉

[dufoq3]

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.

[Jackie003]

Sorry, Of course I meant Starts with 🙂 

[Khushidesai0109]

@Jackie003 

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!!