Announcement

Collapse
No announcement yet.

Insert Character

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert Character

    Hello!

    Can anyone tell me the proper VBA syntax for the following example?
    Code:
    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.

  • #2
    Re: Insert Character

    Hi Macropheliac,

    Code:
    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?

    Comment


    • #3
      Re: Insert Character

      Try this
      Code:
      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

      New users should read the Forum Rules before posting

      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.

      Comment


      • #4
        Re: Insert Character

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

        Comment

        Working...
        X