Manipulating Strings (Cells) in a Macro.

[heazlem]

I am writing a macro and need help manipulating the characters in an address field. Here is a sample line of data;

MISS CARMEN ANDREA BAKS OR

All of this information is contained in one cell. I want the macro to do 2 things.

1) I want it to delete the last 3 characters in the cell if they are _OR

2) I want to separate the first and last names into different cells. In this case the first name would be Carmen Andrea and the surname would be Baks.

I Have been working on this for some time and would really appreciate some advice.

thanks

Mark.

 

[NateO]

Hi Nate nice code. Wont you need to loop through the cells?

Thanks. Sure.

EDIT: Nate, didnt seem to work with multiple christian names. Result was just one christian name with no surname.

Worked fine for me on the first example, the reasoning for the ElseIf structure. Is your column wide enough?

Still, not robust enough for the quandary at hand. The W.F. approach above should be, as long as you stack all of the potential prefixes where I have MRS, etc... You could easy fill the cells with R1C1 notation procedure and do a value transfer, i.e., .value = .value.

 

[NateO]

The following seems to fire eh:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> StringManip2()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range, y <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, a <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, b <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, c <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> Range([c1], [a65536].End(3)(1, 3))
    y = WorksheetFunction.Substitute(cl(1, -1), " OR", vbNullString)
    a = Right$(y, Len - InStrRev(y, ChrW$(32)))
    <SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">CBool</SPAN>(InStrB("|Jr|III|IV|V|VI|", "|" & a & "|")) <SPAN style="color:darkblue">Then</SPAN>
        c = Trim$(WorksheetFunction.Substitute(y, a, vbNullString))
        b = Right$(c, Len(c) - InStrRev(c, ChrW$(32)))
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    cl = Trim$(b & ChrW$(32) & a): <SPAN style="color:darkblue">Let</SPAN> b = <SPAN style="color:darkblue">Empty</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">With</SPAN> Range([b1], [a65536].End(3)(1, 2))
    .FormulaR1C1 = _
    "=TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(RC[-1],LEN(RC[-1])-SUMPRODUCT(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],{""MISS"",""MR"",""MRS""},"""")))),""OR"",""""),RC[1],""""))"
    .Value = .Value
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

The InstrRev function requires VB. 6+