Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Insert Character

  1. #1
    Join Date
    12th February 2006
    Posts
    262

    Insert Character

    Hello!

    Can anyone tell me the proper VBA syntax for the following example?
    VB:
    Sub Insert_Character() 
        Select Case Len(ActiveCell) 
        Case Is = 3 
            ActiveCell.Value = [First 2 Characters] & "X" & [Last Character] 
        Case Is = 4 
            ActiveCell.Value = [First Character] & "X" & [Last 3 Characters] 
        End Select 
    End Sub 
    
    
    In other words: If the character count is 3, I would like to place an "X" between the 2nd and 3rd character.
    If the character count is 4, I would like to place an "X" between the 1st and 2nd character.

    Any help will be sincerely appreciated.




    I know I'll feel like an idiot when I read the answer, but my brain just won't work today.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th July 2005
    Posts
    214

    Re: Insert Character

    Hi Macropheliac,

    VB:
    Sub Insert_Character() 
        Select Case Len(ActiveCell) 
        Case Is = 3 
            ActiveCell.Value = Left(ActiveCell, 2) & "X" & Right(ActiveCell, 1) 
        Case Is = 4 
            ActiveCell.Value = Left(ActiveCell, 1) & "X" & Right(ActiveCell, 3) 
        End Select 
    End Sub 
    
    
    Stefan
    p.s.
    What if the cell contained 3 characters and you run the code to insert X and you run it again, it will now see four characters and insert another X. Desidered?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,345

    Re: Insert Character

    Try this
    VB:
    Option Explicit 
     
    Sub Insert_Character() 
        Dim sTxt As String 
        sTxt = ActiveCell.Value 
        Select Case Len(ActiveCell) 
        Case Is = 3 
            ActiveCell.Value = Left(sTxt, 2) & "X" & Right(sTxt, 1) 
        Case Is = 4 
            ActiveCell.Value = Left(sTxt, 1) & "X" & Right(sTxt, 3) 
        End Select 
    End Sub 
    
    
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    12th February 2006
    Posts
    262

    Re: Insert Character

    Thanks, Stefan and Roy!
    The suggestion was exactly what I was looking for.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: April 20th, 2007, 03:22
  2. Insert unusual character
    By jfontein in forum EXCEL HELP
    Replies: 4
    Last Post: October 12th, 2005, 16:46
  3. Insert Character before & after value
    By karmakula in forum EXCEL HELP
    Replies: 8
    Last Post: November 5th, 2004, 01:09
  4. Check cell value and insert character [SOLVED]
    By PNeely in forum EXCEL HELP
    Replies: 2
    Last Post: April 30th, 2003, 00:07

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
  •  
porno