CODEWITHSUNDEEP
stringexcelvbadate
karips
karips

Reputation: 151

Fixing date in sheet (searching for string)

I have two columns with values such as:

5 - 11 octubre                             2011
5 - 11 octubre                             2011
5 - 11 octubre                             2011
12 - 18 diciembre                          2011
30 noviembre - 6 diciembre                 2012
30 noviembre - 6 diciembre                 2012
18 - 24 septiembre_2012                    2012
18 - 24 septiembre_2012                    2012
22 - 28 abril_2013                         2013
22 - 28 abril_2013                         2013

I want to take the last day and put it together into one column such as:

2011/10/11
2011/10/11
2011/10/11
2011/12/18
2011/12/06
2011/12/06
2012/09/24
2012/09/24
2013/04/28
2013/04/28

But I can't find a way to do so. Could anybody help me?

Right now I have this: (the messed date is in D and year in I)

Sub Data()
With ActiveSheet
LASTROW = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

   For b = LASTROW To 1 Step -1
        Cells(b, 4) = Application.Clean(Application.Trim(Cells(b, 4)))
    Next




For i = 1 To LASTROW

Valor = Right(Range("D2").Offset(i, 0).Value, 12)
 Numero = Right(Range("D2").Offset(i, 0).Value, 4)

If InStr(1, Valor, "enero") > 0 Then
Range("E2").Offset(i, 0).Value = "Enero"
End If

If InStr(1, Valor, "febrero") > 0 Then
Range("E2").Offset(i, 0).Value = "Febrero"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "marzo") > 0 Then
Range("E2").Offset(i, 0).Value = "Marzo"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "abril") > 0 Then
Range("E2").Offset(i, 0).Value = "Abril"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "mayo") > 0 Then
Range("E2").Offset(i, 0).Value = "Mayo"
End If


If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "junio") > 0 Then
Range("E2").Offset(i, 0).Value = "Junio"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "julio") > 0 Then
Range("E2").Offset(i, 0).Value = "Julio"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "agosto") > 0 Then
Range("E2").Offset(i, 0).Value = "Agosto"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "septiembre") > 0 Then
Range("E2").Offset(i, 0).Value = "Septiembre"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "octubre") > 0 Then
Range("E2").Offset(i, 0).Value = "Octubre"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "noviembre") > 0 Then
Range("E2").Offset(i, 0).Value = "Noviembre"
End If

If InStr(1, ActiveSheet.Range("D2").Offset(i, 0).Value, "diciembre") > 0 Then
Range("E2").Offset(i, 0).Value = "Diciembre"
End If
Next i

End Sub

I'm thinking about check if "Numero" is a number and if it is, I don't know how to delete it from the main string... If you guys could help me...

Upvotes: 1

Views: 116

Answers (1)

Jerry
Jerry

Reputation: 71538

IF you don't mind using an excel formula, you could use this, assuming that the date range is in cell A1 and the year in cell B1:

=(MID(A1,FIND("-",A1)+2,IFERROR(FIND("_",A1)-FIND("-",A1)-2,30))&" "&B1)*1

This basically finds - and uses it to get the date and month, stopping at _ if it finds one, and concatenates it to the year. The *1 is to convert the string to number, and you only have to format the column in which you have the formula to yyyy/mm/dd.

Upvotes: 2

Related Questions