convert character+number string into date

[franki]

I am trying to extract a character + number combination then turn it into a date.
For example: aa23 = 1/23/2001.
The difficult part is my data is sometimes presented as 11aa23y or LYAA23. The key pattern is always 2 letters followed by 2 numbers. I need to extract that, then convert it using a lookup.
I have searched and searched. I have a lot of experience with excel, but not too in depth so I may not be using the correct terminology in my search.
Any help is appreciated.

 

[Marcílio_Lobão]

franki, Good afternoon.

Is there any pattern for the data in the cells?

Are these two examples the only variations?

Is the sequence of letters always aa plus any two-digit number?

Please give us more information to facilitate our help to you.

 

[franki]

Ah. good question.
1st letter - month
a - January
b- February
etc.
2nd letter - year
a-2001
b-2002
c-2003
Numbers
23 - 23rd day of month

so aa23= 1/23/2001
bb11 = 2/11/2002

There is always a combination of letter,letter,number,number. sometimes there is additional numbers or letters before or after, but not both.
There may be 12aa23 where 12 is not used or aa23k.
another example is 41x2kc21. only kc21 is relevant.
and sometimes I get data like ch16m, where again, only the ch16 is relevant.

 

[Marcílio_Lobão]

franki, Good afternoon.

I believe that isn't the most elegant formula that I created.
But until a new one appears it can be used if it works for you.

A1 --> zxcvbb10

B1 --> =IF(ISNUMBER(--RIGHT(A1,1)),RIGHT(A1,4),LEFT(RIGHT(A1,5),4))

Please, tell us if it worked for you.

I hope it helps.

 

[franki]

franki, Good afternoon.

I believe that isn't the most elegant formula that I created.
But until a new one appears it can be used if it works for you.

A1 --> zxcvbb10

B1 --> =IF(ISNUMBER(--RIGHT(A1,1)),RIGHT(A1,4),LEFT(RIGHT(A1,5),4))

Please, tell us if it worked for you.

I hope it helps.

It showed good results at first.
After I pulled my data to analyze I plugged the formula in.
The result of 79KM11 = 1
When I type 79KM11 into the cell the result changed from 1 to KM11.
I am not sure why.

 

[franki]

franki, Good afternoon.

I believe that isn't the most elegant formula that I created.
But until a new one appears it can be used if it works for you.

A1 --> zxcvbb10

B1 --> =IF(ISNUMBER(--RIGHT(A1,1)),RIGHT(A1,4),LEFT(RIGHT(A1,5),4))

Please, tell us if it worked for you.

I hope it helps.

It worked when testing, but after I plug into my data the results are different
example:
I type 58gd02 into A1. using formula provided result = gd02
in my data from database I use formula for 58gd02, result =2
I tried changing format, but that did not effect the result.

 

[Marcílio_Lobão]

franki, Good evening.

Very good your tests.

These indicates that possibly the data in your database is not just what it appears to be.

It sure has some spaces after the last digit of your data.

So when you type, you only type the characters to the last and nothing else after it.

When you type 58gd02 if you put the cursor in the cell it will be stopped shortly after 2.
Try placing the cursor in the cell that has an original data and make sure that it will probably stop after a few spaces.

These data were probably imported from some other system.

If this is happening do the following test:

In a original data delete the spaces to the last digit and verify if the formula works.
They may be only spaces but may be nonprinting characters as well.

In order not to get a huge message about what it can be, do these tests and send me the answer on what happened and we'll already provide a quick solution to that.

Can you change these original data or do you have to work with them the way they are?

 

[Rick Rothstein]

Here is a UDF (user defined function) that will do what you want. Note, though, that it returns the date's serial number so you will have to use Cell Formatting to make it display with the date format of your choosing.

[table="width: 500"]
[tr]
	[td]Function GetDate(S As String) As Date
  Dim X As Long, Txt As String
  For X = 1 To Len(S)
    If Mid(S, X, 4) Like "[A-La-l][A-Za-z]##" Then
      Txt = UCase(Mid(S, X, 4))
      GetDate = DateSerial(2000 + Asc(Mid(Txt, 2, 1)) - 64, Asc(Txt) - 64, Right(Txt, 2))
    End If
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetDate just like it was a built-in Excel function. For example,

=GetDate(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[franki]

Here is a UDF (user defined function) that will do what you want. Note, though, that it returns the date's serial number so you will have to use Cell Formatting to make it display with the date format of your choosing.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function GetDate(S As String) As Date
  Dim X As Long, Txt As String
  For X = 1 To Len(S)
    If Mid(S, X, 4) Like "[A-La-l][A-Za-z]##" Then
      Txt = UCase(Mid(S, X, 4))
      GetDate = DateSerial(2000 + Asc(Mid(Txt, 2, 1)) - 64, Asc(Txt) - 64, Right(Txt, 2))
    End If
  Next
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetDate just like it was a built-in Excel function. For example,

=GetDate(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you. This works, albeit my months are different for different products and not all letters are used for years. In addition, some products use 12 as the 12th day of the month, other products use 21 for 12th day of the month.
I will research this function to see if I can modify it for my application.

 

[franki]

franki, Good evening.

Very good your tests.

These indicates that possibly the data in your database is not just what it appears to be.

It sure has some spaces after the last digit of your data.

So when you type, you only type the characters to the last and nothing else after it.

When you type 58gd02 if you put the cursor in the cell it will be stopped shortly after 2.
Try placing the cursor in the cell that has an original data and make sure that it will probably stop after a few spaces.

These data were probably imported from some other system.

If this is happening do the following test:

In a original data delete the spaces to the last digit and verify if the formula works.
They may be only spaces but may be nonprinting characters as well.

In order not to get a huge message about what it can be, do these tests and send me the answer on what happened and we'll already provide a quick solution to that.

Can you change these original data or do you have to work with them the way they are?

Ah!
I used the trim function and it works without fail.

Thank you!