Excel 2007 - If column starts with the number 5, add three zeroes, if has spaces...
Posted on 2010-11-15
I have a column of credit card numbers, but to use these numbers in my work, I need to insert three zeroes in front of every card number that starts with the number 5. Also, I need to have removed any spaces in the numbers.
So if a value in the column had a number like this: 5432 7892 0987 6665 (not a real number, btw) the function would take it and change it to look like this: 0005432789209876665
And the format of the cell for the reformatted credit card number needs to be in a text format.
Seems simple enough, but beyond me.