Excel 2007 : Problem with text to column and dates

[sabrinigreen]

Hello - I am working with a database of cemetery info. I have names and dates. I am splitting the names into first name, last name. However, some of the names have middle initials, Jr., Sr., whatever and so those go into more columns than I need and I manually put them back in.

For example,
Earll, Joe E., Jr.

I set comma as the delimiter and I get three new columns instead of two. Also, this creates a space before Joe in the second column. I have gotten around that by doing a trim.

However, my biggest problem is the dates.
Jan 8 1955 - July 8 1989

Isn't recognized as a date b/c there's no comma

Jan. 8, 1955

Isn't recognized as a date b/c there's a period.

Jan 1955 - Dec 1955

Isn't recognized b/c there's no day.


And I can't even put all the numbers as dates b/c some are years that were stored as text.
I'm attaching the document to make it clearer, but this is just taking me way too long since I have many of these to do.

Oh yeah, and I have some 1800 dates and it doesn't recognize those. I downloaded the add-in but it doesn't seem to be working.

As always, your help is much appreciated.

[mrice]

For the names, I would suggest a couple of user defined functions.

Please Login or Register  to view this content.

Paste these into a new module in the VBA editor (Alt F11, Insert, Module) and then enter

=BeforeFirstComma(B2)

in C2 and

=AfterFirstComma(B2)

in D2.

The dates are more tricky as you point out. It would help if you could specify what you want as your output. Is it just the year values (which should be quite straight forward) or do you want the month and day if it is available?

[sabrinigreen]

The way they want it is with month and date if available, in the format, 3/21/1912 or m/dd/yyy.

Thank you

[sabrinigreen]

Btw, that VBA and formula worked for the names, thanks. I just had to manually adjust for a few that didn't have commas.

[sabrinigreen]

Actually, it just has to be m/d/yyyy - it doesn't have to be two digits for day or month if there are not actually two digits - like 3/1/1912 is fine

[sabrinigreen]

And if there is no date, it should say "No Date", but I am okay with putting that in manually if need be

[sabrinigreen]

Oh yes, and just for good measure, I need the last name is all caps. I know Excel doesn't have that feature, so I've been pasting them into Word and then doing Font All Caps and pasting it back into Excel.

[abousetta]

The dates one is kinda tough so I will differ on this one. As for the upper case there should be a function called UPPER that will convert a string to all upper case. If not then you can use this UDF:

Please Login or Register  to view this content.

[sabrinigreen]

Where do I enter that text to get just that column all caps?
---------------------
You're right about the upper function, so much easier.

[mrice]

Here are a further two functions for the date columns.

Please Login or Register  to view this content.

I've made an assumption that if there is only one date, then this refers to date of death. If only a month and year are given, the functions give the first of the month as the date - is this OK?

[sabrinigreen]

Actually, if there is only one date, it seems to be the date of birth in most cases (they were bought pre-need and never got the death date etched in).

And, no it can't say the first of the month, as that would be giving erroneous information. If no day is listed on stone, it just has to be m/yyyy

If there's a way I could sort it to get all the no day ones together than maybe I could do a custom number thing, but the data sort just seems to go by values.

[sabrinigreen]

But if the month year only thing is impossible to work around, I can just manually go and change those if you have it like the first of the month. I would just need the formula to reflect only one date being the birth date instead of death.

[mrice]

OK, here is a somewhat more elaborate version.

Please Login or Register  to view this content.

[sabrinigreen]

Thank you. I put the code in the VBA module - but what do I type in the cells to get what I need?
---
Wait, I get it, I put =DateOfBirth(e2) or whatever. Now is there any way that I can get this saved to my excel rather than just the workbook?

Thank you so much!

[mrice]

Correct! Well deduced .

Try saving an empty workbook with just the macros as an add -in (.xlam) and opening this add-in.

[sabrinigreen]

Well, that all works swimmingly, unfortunately the macros keep crashing my excel.

[sabrinigreen]

Okay, I seem to have gotten it to not crash, but now I can't find/replace ??? with "No Date" because it replaces any/everything

[sabrinigreen]

Never mind, I adjusted the code to suit. Now my saved code is asking for a password, but I'll post that issue in another thread. Thanks.