Removing one digit in the left side of text in a cell 
Author Message
 Removing one digit in the left side of text in a cell

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.


Wed, 18 Jun 1902 08:00:00 GMT  
 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.



Wed, 18 Jun 1902 08:00:00 GMT  
 Removing one digit in the left side of text in a cell
how about:

    For Each ce In Selection
        ce.Value = DateValue(Right(ce.Value, 2) & "/" & Mid(ce.Value, 2, 2) &
"/" & Mid(ce.Value, 4, 2))
    Next ce

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.

--

Dave Peterson



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Starting excel'02 so that menu one left side showing (Opening a work, etc)

2. Starting excel'02 so that menu one left side showing (Opening a work, etc)

3. Move Comment Box Flag And Comment Display To Left Side Of Cell

4. Sorting by number digits from left to right instead of right to left

5. Removing one digit

6. import text along the left side

7. Prints emails leaving huge Left side margin

8. Left justify text AND wordwrap text over a range of cells

9. Left justify text AND wordwrap text over a range of cells

10. AutoConvert digit to text in words (15-digit max) for Cheque validation


 
Powered by phpBB® Forum Software © phpBB Group