Excel 2007 : Problem with text to column and dates

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

Function BeforeFirstComma(MyString As String) As String
BeforeFirstComma = Trim(Left(MyString, InStr(MyString, ",") - 1))
End Function

Function AfterFirstComma(MyString As String) As String
AfterFirstComma = Trim(Right(MyString, Len(MyString) - InStr(MyString, ",")))
End Function

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?

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:

Function UpperCase(myString As String) As String

UpperCase = UCase(myString)

End Function

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

Here are a further two functions for the date columns.

Function DateOfBirth(MyRange As Range)
If InStr(MyRange, "-") > 0 Then
    MyString = Trim(Left(MyRange, InStr(MyRange, "-") - 1))
    DateOfBirth = "???"
    If IsNumeric(MyString) Then
        DateOfBirth = Val(MyString)
    ElseIf IsDate(MyString) Then
        DateOfBirth = Str(DateValue(MyString))
    End If
Else
    DateOfBirth = "???"
End If
End Function

Function DateOfDeath(MyRange As Range)
If InStr(MyRange, "-") > 0 Then
    MyString = Trim(Right(MyRange, Len(MyRange) - InStr(MyRange, "-")))
    DateOfDeath = "???"
    If IsNumeric(MyString) Then
        DateOfDeath = Val(MyString)
    ElseIf IsDate(MyString) Then
        DateOfDeath = Str(DateValue(MyString))
    End If
Else
    DateOfDeath = "???"
End If
End Function

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?

OK, here is a somewhat more elaborate version.

Function BeforeFirstComma(MyString As String) As String
BeforeFirstComma = UCase(Trim(Left(MyString, InStr(MyString, ",") - 1)))
End Function

Function AfterFirstComma(MyString As String) As String
AfterFirstComma = Trim(Right(MyString, Len(MyString) - InStr(MyString, ",")))
End Function

Function DateOfBirth(MyRange As Range)
If InStr(MyRange, "-") > 0 And Len(MyRange) - Len(WorksheetFunction.Substitute(MyRange, "-", "")) = 1 Then
    MyString = Trim(Left(MyRange, InStr(MyRange, "-") - 1))
    DateOfBirth = "???"
    If IsNumeric(MyString) Then
        DateOfBirth = Val(MyString)
    ElseIf IsDate(MyString) Then
        DateOfBirth = Str(DateValue(MyString))
        If Day(DateValue(MyString)) = 1 Then
            'count numbers
            For X = 1 To Len(MyString)
                If IsNumeric(Mid(MyString, X, 1)) Then
                    NumberCount = NumberCount + 1
                End If
            Next X
            If NumberCount < 5 Then
                DateOfBirth = Format(DateValue(MyString), "mm/yyyy")
            End If
        End If
    End If
Else
    MyString = Trim(MyRange)
    DateOfBirth = "???"
    If IsNumeric(MyString) Then
        DateOfBirth = Val(MyString)
    ElseIf IsDate(MyString) Then
        DateOfBirth = Str(DateValue(MyString))
    End If
End If
End Function

Function DateOfDeath(MyRange As Range)
If InStr(MyRange, "-") > 0 And Len(MyRange) - Len(WorksheetFunction.Substitute(MyRange, "-", "")) = 1 Then
    MyString = Trim(Right(MyRange, Len(MyRange) - InStr(MyRange, "-")))
    DateOfDeath = "???"
    If IsNumeric(MyString) Then
        DateOfDeath = Val(MyString)
    ElseIf IsDate(MyString) Then
        DateOfDeath = Str(DateValue(MyString))
        If Day(DateValue(MyString)) = 1 Then
            'count numbers
            For X = 1 To Len(MyString)
                If IsNumeric(Mid(MyString, X, 1)) Then
                    NumberCount = NumberCount + 1
                End If
            Next X
            If NumberCount < 5 Then
                DateOfDeath = Format(DateValue(MyString), "mm/yyyy")
            End If
        End If
    End If
Else
    DateOfDeath = "???"
End If
End Function

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!

Correct! Well deduced .

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