Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Formula to add a ":" after every 2 characters in a cell?

  1. #1
    New Member
    Join Date
    May 2009
    Location
    Nyack, NY
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Formula to add a ":" after every 2 characters in a cell?

    Hi Everyone,
    I have searched online and in help but can't seem to find the best solution...

    I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

    Thanks in advance!!!!

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Formula to add a ":" after every 2 characters in a cell?

    Hello and welcome to MrExcel.

    Try

    =LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&RIGHT(A1,2)
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula to add a ":" after every 2 characters in a cell?

    =LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&RIGHT(A1,2)

    Might be a better way than this but this should work

  4. #4
    New Member
    Join Date
    May 2009
    Location
    Nyack, NY
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Formula to add a ":" after every 2 characters in a cell?

    Wow, those responses were super quick! Thank you so much everyone, and thanks for the welcome VoG.

    I will give these a try and let you know how they work!

  5. #5
    New Member
    Join Date
    May 2009
    Location
    Nyack, NY
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Formula to add a ":" after every 2 characters in a cell?

    That worked great - thank you again.

    My next question - is there any way to have that formula work for the cell that the value is in? If there is an easy solution, I'd appreciate your sharing it.


  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Formula to add a ":" after every 2 characters in a cell?

    Try this: press ALT + F11 to open the Visual Basic Editor then Insert > Module then paste into the white space on the right

    Code:
    Sub test()
    Dim c As Range
    For Each c In Selection
        c.Value = Left(c, 2) & ":" & Mid(c, 3, 2) & ":" & Mid(c, 5, 2) & ":" & Mid(c, 7, 2) & ":" & Mid(c, 9, 2) & ":" & Right(c, 2)
    Next c
    End Sub
    Press ALT + Q. Select the range of values to convert, Tools > Macro > Macros, click on test then click the Run button.

    This is irreversible - there is no Undo.
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    New Member
    Join Date
    May 2009
    Location
    Nyack, NY
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Formula to add a ":" after every 2 characters in a cell?

    That worked perfectly! Thank you so much!!!! Have a great day.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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