Format a creditcard number with spaces

[chappy72]

Hello everybody

I'm trying around with Power Query again. One thing I was not able to solve so far:
in a column there is a creditcard number with the 16 digits:
1234567890123456
Now I was not able to find a function to reformat it with spaces:
1234 5678 9012 3456

Does anybody have an idea how to do it? In Excel it's easy with TEXT.

Thanks a lot!
Daniel

 

[Ken Puls]

Using a custom formula (assuming that your calling is called CC):

Text.Start([CC],4)&" "&Text.Range([CC],4,4)&" "&Text.Range([CC],8,4)&" "&Text.Range([CC],12,4)

Alternately, you could split the column by number of characters (using 4), then merging the resulting columns back together using a space as the delimiter.

 

[chappy72]

Hi Ken

ups...it's a bit late I think and I'm tired. Well of course, so easy.

Thanks a lot! Perfect. I should have known it by myself but I was so fixed to a TEXT or Format-function I didn't thought about mid.

Best wishes
Daniel

 

[MarcelBeug]

Another alternative would be:

Text.Combine(Splitter.SplitTextByRepeatedLengths(4)([CC])," ")

 

[chappy72]

perfect, thanks a lot!