Tell me more ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.
up vote 3 down vote favorite

I have this credit card number in a Google Docs spreadsheet 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?

share|improve this question
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

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
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
up vote 0 down vote

Try this:

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

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


OUTPUT:

1st: 1234-5678-9012-3456

2nd: 1234 5678 9012 3456

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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