Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    196
    Thanks
    52
    Thanked 22 Times in 18 Posts

    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...
    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.

  2. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    83
    Thanks
    12
    Thanked 5 Times in 5 Posts
    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
    Maria
    Simmo7
    Victoria, Australia

  3. The Following User Says Thank You to simmo7 For This Useful Post:

    Mike Feury (2015-07-02)

  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,213
    Thanks
    17
    Thanked 45 Times in 44 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.

  5. #4
    2 Star Lounger
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    196
    Thanks
    52
    Thanked 22 Times in 18 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •