Removing one digit in the left side of text in a cell
Here are two options. The first one drops the left most character. The
second one check to see if the left most
ASCII character code is for the box. To use, select all the cells in
question and run macro. Test before using, I tested but don't have real
data.
Sub DropLeftChar()
Dim theCell As Range
Application.ScreenUpdating = False
For Each theCell In Selection
theCell = Mid(theCell, 2, 1) & "/" & Mid(theCell, 4, 2) & "/" &
Right(theCell, 2)
theCell.NumberFormat = "mm/dd/yy"
Next theCell
Application.ScreenUpdating = True
End Sub
Sub RemoveLeftChar()
Dim theCell As Range
Application.ScreenUpdating = False
For Each theCell In Selection
theCell = Left(theCell, 3) & "/" & Mid(theCell, 4, 2) & "/" &
Right(theCell, 2)
If (Left(theCell, 1) = Chr(127)) Then
theCell = Right(theCell, Len(theCell) - 1)
End If
theCell.NumberFormat = "mm/dd/yy"
Next theCell
Application.ScreenUpdating = True
End Sub
HTH
Nelson
Kansas City, MO USA
Quote:
> Hi everyone,
> Small question?
> I receive imported reports in Excel 97. The data in Cell A1 starts with a
small
> square (I think it has to do with page start/end for printing
instructions) and
> the date. So Cell A1 has "#070200" I used # instead of the small square
> character since it is not in my keybord.
> My goal is the remove the Small square and have Excel show A1 with the
Date
> format mm/dd/yy. If you please provide me with a VBA code to do this.
> Thanks in Advance.