Replace sepcific text with specific numbers

[etaver87]

Hi everyone,

In the attached excel I have the problem that the date are not displayed in a consistent way.

In columns C and D the month March, May October and December are not displayed in the right way. It is displayed as 01.Mar.2016 when it should display 01.03.2016 .

I would need an easy way, preferably with a macro, that replaces for these months the text with the corresponding month number. For example for March, replace Mar with 03, May with 05, Oct with 10, Dec with 12. In that I can actually compute the days by subtracting C from D.

Many thanks for your help.

Best regards,
Enrico

[MarvinP]

Hi Enrico,

No need for VBA when an Excel formula will do the trick. Here is the formula in F2

Please Login or Register  to view this content.

Pull this formula in F2 over to G2 and then autofill down to the bottom. Then copy and paste columns F and G over C and D using Values Only and format cells to date.

See the attached.
DateValue fixer on some months.xlsx

[IonutC]

Hi,

If yu need a macro use this and edit it accordingly.

Please Login or Register  to view this content.

[etaver87]

Hi Marvin P

Unfortunately, that doesn't work for me. I would rather do it with a replacing as columns A, B, C, D and E are from an external source and updated every week. Once copied in, I would prefer a button that I can assign a macro to which replaces the text months to numbers.

[etaver87]

Thanks a lot, but It should only replace in columns C and D, not the entire worksheet (so not in the names for instances that are in columsn A and B).

[IonutC]

Hi Marvin P

Unfortunately, that doesn't work for me. I would rather do it with a replacing as columns A, B, C, D and E are from an external source and updated every week. Once copied in, I would prefer a button that I can assign a macro to which replaces the text months to numbers.

Etaver use my macro and will do the Job. You can assign it to a button and all is fine.

[AlKey]

Not sure why there is a problem but with this simple formula I was able to convert all dates without any issues.
Please see attached file.

Please Login or Register  to view this content.

[etaver87]

Etaver use my macro and will do the Job. You can assign it to a button and all is fine.

That's what I would love and it works for the whole sheet but I only need to find/replace in columns C and D. Is that possible?

[IonutC]

try this one

Please Login or Register  to view this content.