Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Characters (Excel 2003 )

    I have string, eg. TH0000005678 in a range, let say in column A, how do I
    insert "-" after the first 2 characters and before the last character
    resulting TH-000000567-8. The string are always 12 Characters.

    Thanks in advance

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Characters (Excel 2003 )

    If the values start in cell A1:
    - Enter the following formula in B1:
    <code>
    =LEFT(A1,2)&"-"&MID(A1,3,9)&"-"&RIGHT(A1,1)
    </code>
    - Fill down as far as needed.
    - If you want to replace the original values:
    - Select the cells in column B.
    - Press Ctrl+C.
    - Select Edit | Paste Special...
    - Select the Values option and click OK.
    - Delete column A.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Characters (Excel 2003 )

    Edit: But Hans is both more experienced and quicker at typing than me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I would do a concatenate in the adjacent cell, for example in cell B1 (for A1):

    =CONCATENATE(LEFT(A1,2),"-",MID(A1,3,9),"-",RIGHT(A1,1))

    Copy down, replace formulas with values and replace old data with new.

    HTH

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Characters (Excel 2003 )

    Hi Hans and Nathan

    Thanks for the formulas, both works well

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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