removing content, reformatting single cells

  • Thread starter Thread starter Amazon
  • Start date Start date
A

Amazon

I've been given a workbook to reformat and I need help.
The predominate challenge is to reformat the contents of single cells, each
containing a letter, a date, (mm-dd-yy) and a number. Example: P Aug 12 03
2. I want to build a macro that will:
1. Find and remove the first letter
2. Reformat the date into another date format
3. remove the final number and place it into an adjacent column.

I spent a few minutes recording a macro while using the "Find" tool, but
after reading the code I decided I don't know enough Visual Basic to pull
this one off by myself. Can anyone help please?

Of course none of this would be necessary had the original author been a
little more careful when setting up the workbook. Now it's fallen to me to
fix it - all 3,000 entries.

Jane
 
You wrote that the date had a format of mm-dd-yy, but then used "Aug 12 03" as
an example. Does this mean that the format changes or that you're a bad typist
<bg>? And what is your final number--Ahhhh, that line was wrapped!

I'm guessing that the example was correct.

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim myYear As Long
Dim myDateStr As String

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constant cells found" & vbLf _
& "Please select a better range and try again."
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
If .Value Like "? ??? ## ## #" _
Or .Value Like "? ??? # ## #" Then

myYear = CLng(Trim(Mid(.Value, 7, 2)))

If myYear > 90 Then
myYear = 1900 + myYear
Else
myYear = 2000 + myYear
End If

myDateStr = Trim(Mid(.Value, 3, 5)) & ", " & myYear

If IsDate(myDateStr) Then
.Offset(0, 1).Value = Right(.Value, 1)
.Value = CDate(Trim(Mid(.Value, 3, 5)) & ", " & myYear)
.NumberFormat = "yyyy/mm/dd"
End If
End If
End With
Next myCell

End Sub

Select your range of cells (I figured you could limit the search to just a
subset of the whole worksheet) and run this macro.

Change the .numberformat to what you want.

But since this replaces the existing data, run it against a test version of your
workbook. (Or close without saving if it doesn't work the way you want.)

That "like" stuff means that I look for patterns. If it doesn't match either of
the patterns, the macro won't change it.
 
Back
Top