+ Reply to Thread
Results 1 to 5 of 5

Insert colon in text in cell

  1. #1
    Registered User
    Join Date
    11-20-2003
    Posts
    18

    Insert colon in text in cell

    didnt't know what to search to find it on the forum, so here is the problem.


    I need to insert colon to places to the left, like "X12345"="X123:45",
    and "X1234"="X12:34" etc.

    I'm sure it's a simple answer for you guys, but not for me

  2. #2
    Max
    Guest

    Re: Insert colon in text in cell

    One way, assuming source data in A1 down
    put in B1: =LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)
    Copy B1 down

    Freeze col B with an in-place: Copy > Paste special > Check "Values" > OK
    Then delete col A
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Phoenix" <Phoenix.22naxb_1138956603.98@excelforum-nospam.com> wrote in
    message news:Phoenix.22naxb_1138956603.98@excelforum-nospam.com...
    >
    > didnt't know what to search to find it on the forum, so here is the
    > problem.
    >
    >
    > I need to insert colon to places to the left, like "X12345"="X123:45",
    >
    > and "X1234"="X12:34" etc.
    >
    > I'm sure it's a simple answer for you guys, but not for me
    >
    >
    > --
    > Phoenix
    > ------------------------------------------------------------------------
    > Phoenix's Profile:

    http://www.excelforum.com/member.php...fo&userid=2921
    > View this thread: http://www.excelforum.com/showthread...hreadid=507993
    >




  3. #3
    Ken Johnson
    Guest

    Re: Insert colon in text in cell

    Hi Phoenix,
    If A1 contains X12345 then =LEFT(A1,LEN(A1)-2) & ":" & RIGHT(A1,2)
    pasted into any other cell will result in that cell displaying X123:45

    Ken Johnson


  4. #4
    Registered User
    Join Date
    11-20-2003
    Posts
    18
    Thanx guys!

    Phoenix

  5. #5
    Max
    Guest

    Re: Insert colon in text in cell

    You're welcome !
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Phoenix" <Phoenix.22nc2y_1138958100.7274@excelforum-nospam.com> wrote in
    message news:Phoenix.22nc2y_1138958100.7274@excelforum-nospam.com...
    >
    > Thanx guys!
    >
    > Phoenix




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1