# Convert Tex to date

#### Flowerstloft

##### New Member
In the 3 date fields (in excel sheet) are cells, about 10%, with date value as text format (Jan 1 2010 12:00AM or Jan 25 2010 12:00AM) randomly. I am looking for VB code to search and reformat or convert any text formated date to date value

The range of the area (three columns of data) is not fixed.

Any help would be great!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
SUPPOSE sheet 1 is like this (copy sheet1 to sheet 2 also from A1 for redoing)

Excel Workbook
ABC
1hdng1hdng2hdng3
210/1/201110/2/201110/3/2011
310/2/201110/3/201110/4/2011
4Jan 1 2010 12:00AM10/4/201110/5/2011
510/4/201110/5/2011Jan 1 2010 12:00AM
6Jan 25 2010 12:00AM10/6/201110/7/2011
710/6/201110/7/2011Jan 25 2010 12:00AM
Sheet1

run this macro test . I have used function already written in the reference quoted under the function codes)

Code:
``````Sub test()
Dim r As Range, r1 As Range, c As Range
Dim j As Long, k As Long, m As Long, n As Long, namemonth As String, monthnumber As Long
Set r = Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown))
Set r1 = r.SpecialCells(xlCellTypeConstants, 2)
For Each c In r1
'c.Select
j = WorksheetFunction.Search(" ", c)
k = WorksheetFunction.Search(" ", c, j + 1)
m = WorksheetFunction.Search(" ", c, k + 1)
n = m + 1
'MsgBox j
'MsgBox k
'MsgBox m
'MsgBox n
namemonth = Left(c, 3)
monthnumber = convertMonthName2Number(namemonth)
c = monthnumber & "/" & Mid(c, j + 1, k - j - 1) & "/" & Mid(c, k + 1, 4) & " " & Mid(c, m + 1, 5) & " " & Right(c, 2)
Next c
End Sub``````

Code:
``````Function convertMonthName2Number(monthName As String) As Integer
'REFERENCE  [U][B]http://www.codeforexcelandoutlook.com/blog/2009[/B][/U]/[U][B]04/converting-month-name-to-number/[/B][/U]
' try to convert month name to actual date type
Dim dtestr As String
dtestr = monthName & "/1/2000"

Dim dte As Date
On Error Resume Next
dte = CDate(dtestr)

If Err.Number <> 0 Then
convertMonthName2Number = -999
Exit Function
End If
On Error GoTo 0

convertMonthName2Number = Month(dte)
End Function``````

Code:
``````Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")
End Sub``````

Thank you! It works

Another possibility. Does this do what you want?

Code:
``````Sub Test()
Dim c As Range
For Each c In Range("A2").CurrentRegion
c = Application.WorksheetFunction.Substitute(c, " ", ", ", 2)
c = Replace(c, "A", " A")
c = Replace(c, "P", " P")
c.NumberFormat = "mm/dd/yy"
Next
End Sub``````

Hey Hotpepper,

WORKS AWESOME! Thank you!

HOTPEPPER

What an elegant and crisp solution. thanks a lot. how did I miss this in the web.

Here is another possibility to consider...

Code:
``````Sub Mission()
Dim Cell As Range
For Each Cell In Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
Cell.Value = CDate(Cell.Value)
Cell.NumberFormat = "mm/dd/yyyy"
Next
End Sub``````

Code:
``````Sub Test()
Dim c As Range
For Each c In Range("A2").[COLOR=red]CurrentRegion[/COLOR]
c = Application.WorksheetFunction.Substitute(c, " ", ", ", 2)
[COLOR=red]c = Replace(c, "A", " A")[/COLOR]
[COLOR=red]c = Replace(c, "P", " P")[/COLOR]
c.NumberFormat = "mm/dd/yy"
Next
End Sub``````
Just a caution... since the CurrentRegion would include the header cells, then the two replace function calls will replace any A's and/or P's in those headers as well as in the cells below them.

Replies
28
Views
536
Replies
21
Views
415
Replies
1
Views
490
Replies
1
Views
81
Replies
2
Views
185

1,196,155
Messages
6,013,761
Members
441,781
Latest member
Gian Carlos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back