6

I have this credit card number in a Google Sheets of the following format:

XXXXXXXXXXXXXXXX

How can I make it display like XXXX XXXX XXXX XXXX or XXXX-XXXX-XXXX-XXXX where they're in four digit blocks?

  • 2
    Have you tried formatting the cell as Plain text and then editing the number to display in the credit card format? – Alex Feb 12 '12 at 23:06
6

With dashes (-):

=CONCATENATE(LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,4)&"-"&RIGHT(A1,4))

or with spaces ():

=CONCATENATE(LEFT(A1,4)&" "&MID(A1,5,4)&" "&MID(A1,9,4)&" "&RIGHT(A1,4))

  • I can't make a comment for some reason (I guess my rep is too low), but it's worth noting that @Raystafarian's answer (currently top rated) can cause serious harm if the spreadsheet is ever analyzed without the formatting (i.e. parsed by an application) as it will hide input mistakes. Example: Input: 1000100010001000 Output: 1000-1000-1000-1000 Input: 10001000100010001000 Output: 1000 1000 1000 1000 – KOVIKO Feb 16 '12 at 23:15
  • @Koviko I know I'm about 3 years late here, but in this case, the formula output is most likely in a different column than the raw data and any application (etc) would most likely use the raw data column, as it may (or may not) just print the formula if using the display column. Your disclaimer is valid if not using the raw data column, thanks! – Raystafarian Apr 15 '15 at 9:29
2

Simpler with a Custom Number format of:

#### #### #### ####

Format > Number > More Formats > Custom Number format ...

1

Try this:

=REGEXREPLACE(text("1234567890123456", "####,####"), ",", "-")

=REGEXREPLACE(text("1234567890123456", "####,####"), ",", " ")


OUTPUT:

1st: 1234-5678-9012-3456

2nd: 1234 5678 9012 3456

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.