Hide the last character in a cell

[jeh]

Hi Folks,

I have a column of names that have a single digit number at the end of each name. e.g. (SMITH,JOE1) What I hope to do is hide the number ,and just the number. Everything else in the cell will be text. Any Ideas?

Thanks for your time,

Jim

[Colo]

Hi.

Please copy this into a standard module.
And after select range you want to change, run this code.
This code will delete last character if it ware a number.

Sub Test()

    Dim varSel, i As Long, j As Integer

    Application.ScreenUpdating = False

    With Intersect(Selection, ActiveSheet.UsedRange)

        If .Count = 1 Then

            If IsNumeric(Right(.Value, 1)) Then

                .Value = Left(.Value, Len(.Value) - 1)

                Exit Sub

            End If

        End If

        varSel = .Value

        .ClearContents

        For i = LBound(varSel, 1) To UBound(varSel, 1)

            For j = LBound(varSel, 2) To UBound(varSel, 2)

                If IsNumeric(Right(varSel(i, j), 1)) Then

                    varSel(i, j) = Left(varSel(i, j), Len(varSel(i, j)) - 1)

                End If

        Next j, i

        .Value = varSel

    End With

    Application.ScreenUpdating = True

End Sub

_________________
With regards,
Colo



[ This Message was edited by: Colo on 2002-04-29 22:26 ]

[Schrödinger’s cat]

Colo's suggestion will delete rather than hide the last character?
Will this work?
=LEFT(A1,LEN(A1)-1)

[jeh]

Thanks Very Much, I can use them both in different applications.

I appreciate it!

Jim

[Chris Davison]

Schrodinger's cat.... alive and well... this should cause some ripples amongst our quantum physics friends !