Removing spaces from credit card numbers

[emsterv]

Hi all, basic excel user here. I need to copy and paste credit card numbers from an excel sheet into a processor. The processor needs the spaces removed, however I can't find a way to easily remove all spaces from the numbers (entered as 1234 1234 1234 1234).

All formatting changes I've made the numbers to something else by either completely changing them (5.160777E+15) or rounding the last digit. I was intending to change the numbers to text formatting and then ctrl+f and replace spaces with nothing.

A quick search online has only confused me further. Can anyone please provide some insight or some step-by-step on how to achieve this and/or how to prevent this issue in the future (for example if we entered the credit card info into a cell formatted a specific way from the get-go).

Thank you! Pulling my hair out here!

[AlKey]

Try SUBSTITUTE function
Enter in B1

Please Login or Register  to view this content.

v	A	B
1	1234 1234 1234 1234	1234123412341234

[Sam Capricci]

this would work if you don't just want to use find " " and replace (left blank)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")," ","")

[ron2k_1]

You can do it via macro, but a quick way would be to use a helper column and then insert this formula and drag down as needed:

Please Login or Register  to view this content.

The above will remove the spaces. Now how the helper column works.
1. Before copying, on the worksheet that has the credit card number (with spaces), insert a column somewhere where it is not in your way
2. Insert the formula above and refer to the column that has the card numbers
3. Copy the contents of the column that has the trimmed credit card numbers
4. Paste as values in your destination sheet for your processor.

Edit: Alkey beat me to it. He and I are suggesting the same.

[Sam Capricci]

I didn't think to go with the shorter version but I see AlKey's is better.

[Logit]

.
And ... if you don't want to drag a formula down the sheet (maybe you have 15,000 rows of card #'s ?), paste this macro into a Routine Module, place a command button on the sheet of card numbers, highlight all the numbers then click the button.

Please Login or Register  to view this content.

Sample workbook attached