Insert Character

Macropheliac · Sep 23rd 2006

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.:confused:

StefanG · Sep 23rd 2006

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?

**royUK** · Sep 23rd 2006

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

Display More

Macropheliac · Sep 23rd 2006

Re: Insert Character

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

Insert Character

Participate now!

Share