+ Reply to Thread
Results 1 to 5 of 5

How to Insert a character between existing characters in a text string?

Hybrid View

forti2ude95 How to Insert a character... 03-08-2012, 12:19 PM
tigeravatar Re: How to Insert a character... 03-08-2012, 12:29 PM
BigBas Re: How to Insert a character... 03-08-2012, 12:33 PM
AlphaFrog Re: How to Insert a character... 03-08-2012, 12:35 PM
forti2ude95 Re: How to Insert a character... 03-08-2012, 12:35 PM
  1. #1
    Registered User
    Join Date
    03-08-2012
    Location
    Houston, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    How to Insert a character between existing characters in a text string?

    How do I add a "-" in two different character positions within a text string?

    For example, the current text string is WF121202 and I need to change it to WF121-2-02 for numerous rows of data.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: How to Insert a character between existing characters in a text string?

    forti2ude95,

    Welcome to the forum!
    If all of the text strings are the same length (8 characters), you can use:
    =LEFT(A1,5)&"-"&MID(A1,6,1)&"-"&RIGHT(A1,2)
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: How to Insert a character between existing characters in a text string?

    If all of the cells follow the same length/structure, the following macro will work without the need for a helper column. (Note, the macro is set to apply to any selected cells)

    Sub AddStuffAndStuff()
        Dim R As Range
        Dim arr(1 To 3) As String
        
        Set R = Selection
        For Each itm In R
            arr(1) = Left(itm, 5)
            arr(2) = Mid(itm, 6, 1)
            arr(3) = Right(itm, 2)
            
            itm.Value = arr(1) & "-" & arr(2) & "-" & arr(3)
        Next itm
        
    End Sub

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,660

    Re: How to Insert a character between existing characters in a text string?

    strOld = "WF121202"
    
    strNew = Left(strOld, 5) & "-" & Mid(strOld, 6, 1) & "-" & Right(strOld, 2)

  5. #5
    Registered User
    Join Date
    03-08-2012
    Location
    Houston, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How to Insert a character between existing characters in a text string?

    Great, it works!! Thanks a bunch!!! Yhis has saved me a lot of time!

    Regards,
    forti2ude95

+ 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