Results 1 to 4 of 4
2015-07-01, 22:47 #1
Adding physical dashes into text-formatted numbers
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...
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.
2015-07-01, 23:43 #2
- Join Date
- Feb 2009
- Kings Park, Victoria, Australia
- Thanked 5 Times in 5 Posts
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.
The Following User Says Thank You to simmo7 For This Useful Post:
2015-07-01, 23:57 #3
- Join Date
- Jan 2001
- La Jolla, CA
- Thanked 67 Times in 63 Posts
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.
2015-07-02, 01:09 #4
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.