Adding physical dashes into text-formatted numbers

Topic

New Reply

I have to do this with a bunch of long numbers in Excel 2013. Let’s say they’re credit card numbers. So I have…
1234 5678 1234 5678
…and I want…
1234-5678-1234-5678

It’s not a custom format thing [####-####-etc], the dashes must be inserted as actual characters, so the final number has 19 characters in total.

A bunch of messing around, plus reading the help, leaves me scratching the brain holder. Help!

I can run VBA if necessary, but I hope there’s an Excel function hiding somewhere.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

Replies

Hi Mike,

Insert a new column to the right of your credit card numbers and if Your credit card numbers are in column A, then in the new column B enter the following formula =LEFT(A1,4)&”-“&MID(A1,6,4)&”-“&MID(A1,11,4)&”-“&RIGHT(A1,4) and copy down the range of your data. you can then either hide column A or convert all formulae in column B to values and delete column A.

Regards,
Maria

Reply | Quote

Alternatively to simmo7, you could create a new column and use substitute if the spaces are to be replaced with the dash.

If the original data with spaces is in the E column, =SUBSTITUTE(E1,” “,”-“) in the F column, say, filled down, will change the spaces to dashes. You can copy column F and paste/special/values and then delete the E column.

Reply | Quote

Thank you kweaver, my fault in the first post, I do not have spaces between the digits, I put them there for easier reading here. SUBSTITUTE I haven’t used before, and it looks to me like global replace would do the same thing more quickly, wouldn’t it?

Maria, you nailed it, thank you too. I never thought of the LEFT, MID, RIGHT functions.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote