# Excel Macro(?) convert text string to date

Closed
painkiller - Apr 2, 2009 at 08:38 AM
noecortez - Jul 3, 2009 at 05:58 PM
Hello,
Maybe this problem is trivial to solve, but I haven't done anything with Excel Macros since University and that was long time ago. Maybe macros aren't even needed.

So, there is one column with ~5000 records, everyone of which all consist of 11 numbers (like 21019910614 ). It is personal identifier in my country and begins with date (in previous example it would look like 21.01.99 - dd.mm.yy ) Other 5 numbers are like ID number.
What is needed? From all these 5000 Records I need to remove last 5 (because I need just date ones) numbers and all remaining turn into Date Format (like I wrote - dd.mm.yy). It is the first part and if we could find any solution to make these records to dates, it would be great.

Hope, I explained the problem enough.
Related:

## 2 responses

This assumes that the data is in column A and that row 1 has header info. So, it starts checking in row 2 of
column A. Also, by using the Do loop until empty assumes there are no blank cells in the column. Otherwise,
the For loop will need to be used.

Hope this helps!

Sub CreateDate()

Dim j
j = 2

Do Until Range("A" & j) = ""

Range("A" & j) = Left(Range("A" & j), 2) & "." & Mid(Range("A" & j), 3, 2) & "." & Mid(Range("A" & j), 5, 2)

j = j + 1

Loop

End Sub
Puedes utilizar el siguiente codigo, solo tecleas ALT+F11 y le das insertar - modulo. Copias el siguiente codigo:
Function ntot(Numero)
Dim Texto
Dim Millones
Dim Miles
Dim Cientos
Dim Decimales
Texto = Numero
Texto = FormatNumber(Texto, 2)
Texto = Right(Space(14) & Texto, 14)
Millones = Mid(Texto, 1, 3)
Miles = Mid(Texto, 5, 3)
Cientos = Mid(Texto, 9, 3)
Decimales = Mid(Texto, 13, 2)
Else
End If
End If
End If
Else
If Miles & Cientos = "000000" Then
Cadena = Cadena & " " & Trim(CadCientos) & " DE PESOS " & Decimales & "/100"
Else
End If
End If
ntot = "(" + Trim(Cadena) + ")"
End Function

Function ConvierteCifra(Texto, SW)
Dim Centena
Dim Decena
Dim txtCentena
Dim txtDecena
Centena = Mid(Texto, 1, 1)
Decena = Mid(Texto, 2, 1)
Select Case Centena
Case "1"
txtCentena = "CIEN"
If Decena & Unidad <> "00" Then
txtCentena = "CIENTO"
End If
Case "2"
txtCentena = "DOSCIENTOS"
Case "3"
txtCentena = "TRESCIENTOS"
Case "4"
txtCentena = "CUATROCIENTOS"
Case "5"
txtCentena = "QUINIENTOS"
Case "6"
txtCentena = "SEISCIENTOS"
Case "7"
txtCentena = "SETECIENTOS"
Case "8"
txtCentena = "OCHOCIENTOS"
Case "9"
txtCentena = "NOVECIENTOS"
End Select

Select Case Decena
Case "1"
txtDecena = "DIEZ"
Case "1"
txtDecena = "ONCE"
Case "2"
txtDecena = "DOCE"
Case "3"
txtDecena = "TRECE"
Case "4"
txtDecena = "CATORCE"
Case "5"
txtDecena = "QUINCE"
Case "6"
txtDecena = "DIECISEIS"
Case "7"
txtDecena = "DIECISIETE"
Case "8"
txtDecena = "DIECIOCHO"
Case "9"
txtDecena = "DIECINUEVE"
End Select
Case "2"
txtDecena = "VEINTE"
txtDecena = "VEINTI"
End If
Case "3"
txtDecena = "TREINTA"
txtDecena = "TREINTA Y "
End If
Case "4"
txtDecena = "CUARENTA"
txtDecena = "CUARENTA Y "
End If
Case "5"
txtDecena = "CINCUENTA"
txtDecena = "CINCUENTA Y "
End If
Case "6"
txtDecena = "SESENTA"
txtDecena = "SESENTA Y "
End If
Case "7"
txtDecena = "SETENTA"
txtDecena = "SETENTA Y "
End If
Case "8"
txtDecena = "OCHENTA"
txtDecena = "OCHENTA Y "
End If
Case "9"
txtDecena = "NOVENTA"
txtDecena = "NOVENTA Y "
End If
End Select

If Decena <> "1" Then
Case "1"
If SW Then
Else
End If
Case "2"
Case "3"
Case "4"
Case "5"
Case "6"
Case "7"
Case "8"
Case "9"
End Select
End If
ConvierteCifra = txtCentena & " " & txtDecena & txtUnidad
End Function

Luego lo mandas llamar desde tu hoja como =ntot(B15), por ejemplo donde B15 es la celda donde esta el numero que quieres convertir a texto.
Para cualquier otro desarrollo estoy a tus ordenes, me puedes escribir a noecortez(arroba)leipzig.com.mx o llamarme al 011+52+55+55654892