Parsing Names & String Manipulation

G

Gravedigger

New Member
Joined
Jul 21, 2010
Messages
12
I am new to this site and drsarao's work on this thread was awesome: http://www.mrexcel.com/forum/showthread.php?t=424754&page=3

I tried to PM him, but no reply. I have a cemetery spreadsheet that I need to work on (hobby) and I need to parse the names (50,000 names). If I give you some examples, can you whip me up something? I am brand new to macros and VB, but I followed the examples on the earlier thread and they worked (not entirely for my situation). If you can get something working I could donate through Paypal.

Here are some of the example names in A1:
unknown
Smith, Paul
Smith, Paul L.
Smith, Paul Lee
Smith, Paul Lee James
Smith, Paul L., Dr.

Smith, Paul L., ,M.D.
Smith, Paul , Mr.
Smith, , Mr.
Smith, Mrs.
Smith, Paul L., , Jr.
Smith, Paul L., Jr.
Smith, Paul L., , Sr.
Smith, Paul L., Sr.
Smith, Paul R., , II
Smith, Paul R., , III
Lewis, Simeon (Infant of)
Norton, C. (Unknown )
Parker, Infant of Everett J.

Spreadsheet has names listed in column A.
I would like:
Column B: (Mr. Mrs. Or Dr.) here
Column C: Last Name (comma gone)
Column D: First Name
Column E: Middle (name, names, initial, initials, or blank if nothing)
Column F: (Jr. Sr. II III M.D. PhD) here
Column G:Extra stuff like: infant of, dau of son of etc Anything in between () but minus the ()
Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi, Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG27Jul57
[COLOR="Navy"]Dim[/COLOR] Title [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dta [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dt2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] T [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Title = Array("Mr.Mrs.Dr.", "Jr.Sr.II.III.M.D.PhD")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dta = Split(Replace(Dn, ",", ""), " ")
        [COLOR="Navy"]For[/COLOR] T = 0 To UBound(Title)
            [COLOR="Navy"]For[/COLOR] Dt = 0 To UBound(Dta)
                [COLOR="Navy"]If[/COLOR] InStr(Dta(Dt), "(") > 0 Or InStr(Dta(Dt), "Infant") > 0 [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]For[/COLOR] Dt2 = Dt To UBound(Dta)
                        Txt = Txt & Dta(Dt2) & Chr(32)
                        Dta(Dt2) = vbNullString
                    [COLOR="Navy"]Next[/COLOR] Dt2
                        Dn.Offset(, 6) = Txt
                        Txt = ""
                [COLOR="Navy"]End[/COLOR] If
            
                    [COLOR="Navy"]If[/COLOR] Dta(Dt) <> "" And InStr(Title(T), Dta(Dt)) > 0 [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] T
                            [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = 0: Dn.Offset(, 1) = Dta(Dt): Dta(Dt) = vbNullString
                            [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = 1: Dn.Offset(, 5) = Dta(Dt): Dta(Dt) = vbNullString
                         [COLOR="Navy"]End[/COLOR] Select
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dt
     [COLOR="Navy"]Next[/COLOR] T
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 0 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 2) = Dta(0)
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 1 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 3) = Dta(1)
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 2 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 4) = Dta(2)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
It seems to be taking any middle initial M. or D. and putting it in column F instead of column E. My guess is the M.D. and Ph.D. rule.
 
Upvote 0
Hi, Try this. The trouble with this sort of thing is , there will always be a situation you have'nt catered for.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG28Jul27
[COLOR="Navy"]Dim[/COLOR] Title [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dta [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dt2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] T [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Title = Array("Mr.", "Mrs.", "Dr.", "Mr", "Mrs", "Dr", "Jr.", "Sr.", "II.", "III.", "M.D.", "PhD.", "Jr", "Sr", "II", "III", "M.D", "PhD")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dta = Split(Replace(Dn, ",", ""), " ")
        [COLOR="Navy"]For[/COLOR] T = 0 To UBound(Title)
            [COLOR="Navy"]For[/COLOR] Dt = 0 To UBound(Dta)
                [COLOR="Navy"]If[/COLOR] InStr(Dta(Dt), "(") > 0 Or InStr(Dta(Dt), "Infant") > 0 [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]For[/COLOR] Dt2 = Dt To UBound(Dta)
                        Txt = Txt & Dta(Dt2) & Chr(32)
                        Dta(Dt2) = vbNullString
                    [COLOR="Navy"]Next[/COLOR] Dt2
                        Dn.Offset(, 6) = Txt
                        Txt = ""
                [COLOR="Navy"]End[/COLOR] If
            
                    [COLOR="Navy"]If[/COLOR] Dta(Dt) <> "" And Title(T) = Dta(Dt) [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] T
                            [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] <= 5: Dn.Offset(, 1) = Dta(Dt): Dta(Dt) = vbNullString
                            [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] > 5: Dn.Offset(, 5) = Dta(Dt): Dta(Dt) = vbNullString
                         [COLOR="Navy"]End[/COLOR] Select
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dt
     [COLOR="Navy"]Next[/COLOR] T
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 0 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 2) = Dta(0)
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 1 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 3) = Dta(1)
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 2 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 4) = Dta(2)
[COLOR="Navy"]If[/COLOR] UBound(Dta) >= 3 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 4) = Dta(2) & " " & Dta(3)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
My hobby of helping cemeteries compile data to help families find loved ones has another spreadsheet for me to start working with (20,000 rows).

Everything is in Column A and it looks like this:
Jones Fred 1822 7-4-1904 24 WI Infantry Co. J Brownville Walnut Grove Nemaha

More examples:
<table style="border-collapse: collapse; width: 660pt;" border="0" cellpadding="0" cellspacing="0" width="876"><col style="width: 110pt;" span="6" width="146"><tbody><tr style="height: 15pt;" height="20"> <td class="xl64" colspan="5" style="height: 15pt; width: 550pt;" height="20" width="730">Brown Abner D. 3-1-1847 12-17-1909 132 IN Infantry Co. A Arnold Arnold Custer </td> <td style="width: 110pt;" width="146">
</td> </tr></tbody></table>
<table style="border-collapse: collapse; width: 660pt;" border="0" cellpadding="0" cellspacing="0" width="876"><col style="width: 110pt;" span="6" width="146"><tbody><tr style="height: 15pt;" height="20"> <td class="xl65" colspan="5" style="height: 15pt; width: 550pt;" height="20" width="730">Brown George 9-1835 5-7-1905 8 US Infantry+ Sgt. Co. A Norden West Norden Keya Paha</td> <td style="width: 110pt;" width="146">
</td> </tr></tbody></table>
Currently it is Last Name- First Name- Middle initial- DateOfBirth -DateOfDeath -CIVIL INFO- City - Cemetery- County

What I need is:
Col B: Last Name
Col C: First name
Col D: Middle Initial or blank if there is no initial listed
Col E: Month # (if one is listed)
Col F: Day # (if one is listed)
Col G: Year
Col H: Month # (if one is listed)
Col I: Day # (if one is listed)
Col J: Year
Col K: Civil War Info
Col L: City
Col M: Cemetery
Col N: County

I know this is not going to be perfect, but the names and dates are the most important. Next important is cemetery and county.

Thanks again!
 
Upvote 0
Hi, This looks a bit more problematical that the last.
Difficult to tell what is a town or a Cememtary at the end,
There a great deal of inconsistency
Have you got a few more examples, to try and get some format for Parsing.
Regards Mick
 
Upvote 0
<table style="border-collapse: collapse; width: 568pt;" border="0" cellpadding="0" cellspacing="0" width="757"><col style="width: 568pt;" width="757"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 568pt;" height="20" width="757">Davis Henry 12-9-1840 10-6-1900 120 OH Infantry Co. B Shickley Shickley Fillmore</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis Henry F. 7-12-1887 16 NY Cavalry Co. F Holdrege Prairie Home Phelps </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Davis Henry J. 4-25-1840 1-29-1937 48 PA Infantry Co. A Ansley Ansley Custer </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis Hiram 12-2-1823 11-7-1891 16 VT Infantry Cpl. Co. C Wymore Murdock Gage</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James A. 1-1847 7-9-1931 36 WI Infantry Co. E Lincoln Wyuka Lancaster</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James E. 1844 9-28-1886 7 OH Cavalry Co. M Stromsburg Pleasant Home Polk </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James H. 3-28-1845 11-8-1915 14 IA Infantry Co. D North Platte North Platte Lincoln </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James H. 11-12-1848 7-6-1915 29 IA Infantry Co. F Bloomington Maple Grove Franklin</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James H. 5-6-1843 12-9-1907 52 MA Infantry+ Co. B Gibbon Riverside Buffalo </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James M. 3-24-1842 11-15-1921 8 KS Infantry Cpl. Co. H Rulo Rulo Richardson </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James M. 4-12-1843 7-14-1915 13 OH Infantry Co. E Lincoln Wyuka Lancaster </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis James R. 12-1838 2-28-1904 34 IA Infantry Cpl. Co. F Craig Craig Burt </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Davis Jerome 6-27-1864 15 IA Infantry Co. G Plattsmouth Oak Hill Cass </td> </tr> </tbody></table> <table style="border-collapse: collapse; width: 568pt;" border="0" cellpadding="0" cellspacing="0" width="757"><col style="width: 568pt;" width="757"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 568pt;" height="20" width="757">Davis John A. 4-11-1833 2-16-1901 65 IN Infantry Sgt. Co. I Wellfleet Lincoln</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Davis John A. 11-1-1846 7-31-1925 28 IA Infantry Co. F Beulah Nuckolls</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Davis John A. 10-7-1839 4-19-1919 29 IA Infantry Co. B Nehawka Otterbein Cass </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Davis John B. 12-1843 11-17-1905* 72 IN Infantry Co. D York Greenwood York </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Davis John C. 1829 9-8-1899 3 WI Infantry Co. F Mt.Pleasant Seward </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Davis John D. 7-1-1848 12-4-1926 97 NY Infantry Co. E Omaha Prospect Hill Douglas

I notice that my actual entries have more spaces between the data.
That may actually help you, but when I post it, they get consolidated.
I can send you part of the spreadsheet. Let me know.

I hope these help.
Thanks,
Tony
</td> </tr> </tbody></table>
 
Upvote 0
Hi, Try this:-
Paste you data in new sheet starting row (2).
NB:- The First loop in this code removes Extra spaces in text (Hopefully)
NB:- This code will alter the Data in column "A", to make it More Parsing friendly, (Altering and entering dates etc) it then trasfered the Data to columns starting column "B" row (2).
NB:- The City and Cemetary data are hard to parse because theres not much rhyme or reason to it.
If you can find any logic to how to parse it let me know,at the Moment is all in the "City" column.
NB:- There are 2 subs here the second is called from the first, Just paste it all as one sub and call it using "Call Pars"
Code: 
[COLOR="Navy"]Sub[/COLOR] Pars()
[COLOR="Navy"]Dim[/COLOR] Ray, r, st [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, dn [COLOR="Navy"]As[/COLOR] Range
Application.ScreenUpdating = False
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
'[COLOR="Green"][B]Removes Extra spaces and "*"[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
Ray = Split(dn, " ")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] r [COLOR="Navy"]In[/COLOR] Ray
        r = Replace(r, "*", "")
        [COLOR="Navy"]If[/COLOR] r <> "" [COLOR="Navy"]Then[/COLOR] st = st & r & " "
    [COLOR="Navy"]Next[/COLOR] r
dn = st: st = ""
[COLOR="Navy"]Next[/COLOR] dn
'[COLOR="Green"][B]Adds Question Mark if no Initial[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
Ray = Split(dn, " ")
[COLOR="Navy"]For[/COLOR] r = 0 To UBound(Ray) - 1
[COLOR="Navy"]If[/COLOR] r = 1 And IsNumeric(Ray(r + 1)) Or r = 1 And IsDate(Ray(r + 1)) [COLOR="Navy"]Then[/COLOR]
st = st & Ray(r) & " ? "
[COLOR="Navy"]Else[/COLOR]
 st = st & Ray(r) & " "
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] r
dn = st: st = ""
[COLOR="Navy"]Next[/COLOR] dn
'[COLOR="Green"][B]Formats all Dates ("dd/mm/yyyy") and Creates Dates from Plain Year[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
    Ray = Split(dn, " ")
        [COLOR="Navy"]For[/COLOR] r = 0 To UBound(Ray)
            [COLOR="Navy"]If[/COLOR] IsDate(Ray(r)) [COLOR="Navy"]Then[/COLOR]
                Ray(r) = Format(Ray(r), "dd/mm/yyyy")
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] r = 3 And IsNumeric(Ray(r)) [COLOR="Navy"]Then[/COLOR]
                Ray(r) = "1/1/" & Ray(r)
            [COLOR="Navy"]End[/COLOR] If
                st = st & Ray(r) & " "
        [COLOR="Navy"]Next[/COLOR] r
dn = st: st = ""
[COLOR="Navy"]Next[/COLOR] dn
'[COLOR="Green"][B]'''''''''''''''''''[/B][/COLOR]
'[COLOR="Green"][B]Add Date of Birth if Missing[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
    Ray = Split(dn, " ")
        [COLOR="Navy"]For[/COLOR] r = 0 To UBound(Ray)
            [COLOR="Navy"]If[/COLOR] r = 3 And Not IsDate(Ray(4)) [COLOR="Navy"]Then[/COLOR]
                st = st & "NO-DOB" & " "
             [COLOR="Navy"]End[/COLOR] If
            st = st & Ray(r) & " "
        [COLOR="Navy"]Next[/COLOR] r
dn = st: st = ""
[COLOR="Navy"]Next[/COLOR] dn
Call AddtoSht '[COLOR="Green"][B] Call sub to Add data to columns[/B][/COLOR]
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] AddtoSht()
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] FdCo [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] oAdd [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
FdCo = False
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
    Ray = Split(dn, " ")
    dn.Offset(, 10) = Mid(dn, InStr(dn, Ray(5)), (InStr(dn, "Co.") + 3) - InStr(dn, Ray(5)))
    dn.Offset(, 11) = Right(dn, Len(dn) - (InStr(dn, "Co") + 1))
    dn.Offset(, 1) = Ray(0)
    dn.Offset(, 2) = Ray(1)
    [COLOR="Navy"]If[/COLOR] Ray(2) = "?" [COLOR="Navy"]Then[/COLOR] dn.Offset(, 3) = "" Else dn.Offset(, 3) = Ray(2)
    
    [COLOR="Navy"]If[/COLOR] IsDate(Ray(3)) [COLOR="Navy"]Then[/COLOR]
        dn.Offset(, 4) = Month(Ray(3)): dn.Offset(, 5) = Split(Ray(3), "/")(0): dn.Offset(, 6) = Year(Ray(3))
    [COLOR="Navy"]ElseIf[/COLOR] Ray(3) = "NO-DOB" [COLOR="Navy"]Then[/COLOR]
        dn.Offset(, 4) = "": dn.Offset(, 5) = "": dn.Offset(, 6) = ""
    [COLOR="Navy"]End[/COLOR] If
    dn.Offset(, 7) = Month(Ray(4)): dn.Offset(, 8) = Split(Ray(4), "/")(0): dn.Offset(, 9) = Year(Ray(4))
    [COLOR="Navy"]Next[/COLOR] dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Wow. That did a great job with most of the data. It seems to be putting the last bit of the Civil war info (Column K) in front of the City/Cem/County cell (Column L). Example :
<table style="border-collapse: collapse; width: 308pt;" border="0" cellpadding="0" cellspacing="0" width="410"><col style="width: 135pt;" width="180"> <col style="width: 173pt;" width="230"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 135pt;" height="20" width="180">32 IA Infantry Co.</td> <td style="width: 173pt;" width="230">. G Madison Crown Hill Madison </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">58 IL Infantry Co.</td> <td>. A Ayr Leroy Adams </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">12 MD Infantry+ Co.</td> <td>. B Crete Riverside Saline </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">156 IN Infantry Co.</td> <td>. C Grand Island GISSH Hall </td> </tr> </tbody></table>
Is there another macro we can run that will take the period off in the front of column L and then take the letter after the period and space and put it at the end of Column K data to look like this?

<table style="border-collapse: collapse; width: 308pt;" border="0" cellpadding="0" cellspacing="0" width="410"><col style="width: 135pt;" width="180"> <col style="width: 173pt;" width="230"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 135pt;" height="20" width="180">32 IA Infantry Co. G</td> <td style="width: 173pt;" width="230">Madison Crown Hill Madison </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">58 IL Infantry Co. A</td> <td>Ayr Leroy Adams </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">12 MD Infantry+ Co. B</td> <td>Crete Riverside Saline </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">156 IN Infantry Co. C</td> <td>Grand Island GISSH Hall </td> </tr> </tbody></table>

Much appreciated (once again).
Thanks-
Tony

<table style="border-collapse: collapse; width: 308pt;" border="0" cellpadding="0" cellspacing="0" width="410"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 135pt;" height="20" width="180">
</td><td style="width: 173pt;" width="230">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td> </tr> </tbody></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Amend Formula for Manipulating Names
Replies
2
Views
274
NorthbyNorthwest
N
R
  • Question Question
Help Parsing Data to Columns
Replies
3
Views
342
Micron
M
I
  • Solved
How to split last name from multiple first/middle names
2
Replies
11
Views
2K
Joe4
Joe4
J
  • Solved
Help with Index using Aggregate with multiple criteria
2
Replies
14
Views
636
Peter_SSs
Peter_SSs
C
  • Solved
First & Last Name in a Single Cell Comparison Checker
2
Replies
12
Views
2K
Phuoc
P

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top