Manipulating Strings (Cells) in a Macro.

heazlem

New Member
Joined
Jan 14, 2004
Messages
2
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi Mark, you could probably just use text to columns to separate the various components of the name (Data|Text To Columns) then just delete the unwanted columns.

Do all the names have Mr/Mrs/Mrs in the first word or is it a mixture? If so, this needs to be taken into consideration so you could just do a replace of these words & the Or - including the trailing space (eg Miss <space>) - with "" prior to using Text to columns. This effectively blanks these words out.

hth
 

heazlem

New Member
Joined
Jan 14, 2004
Messages
2
The problem is that the length of the fields can vary considerably. There can also be multiple middle names. For eaxample;

MR FRED SMITH
ALEXANDER JONES OR
MISS AMY NG
SEBESTIAN MICHAEL ADAM McDONALD

Mark.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi Mark, I think the easiest way is to do this in chunks. Copy the data to a new sheet. Then one by one replace the unwanted components being probably Mr, Mrs, Miss and Or.

eg
1. Copy the data to a new sheet
2. Select Edit|Replace from the menu
3. In the Find What box enter the text you dont want any more including preceding and trailing spaces.
eg "Mr" would normally have a space after this before the christian name so in the Find What box enter Mr then one space.
4. Leave the Replace With box blank then click the Replace All button.

Repeat steps 3-4 for all your unwanted parts of the name.

Now you will have data that looks like this...

FRED SMITH
ALEXANDER JONES
AMY NG
SEBESTIAN MICHAEL ADAM McDONALD

1. Select the column then select Data|Text to Columns from the menu.
2. Choose the delimited option & click Next
3. Uncheck the Tab checkbox and click the Space checkbox then click Finish

You have now separated all the names so its quite easy to join the christian names together by simple formulas.

hth
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841

ADVERTISEMENT

I had to do simmilar task once,
I used a loop to count spaces in the strings then broke out all each word into a varable of an array. Then wrote conditions to find specific things to strike.
MR, MR*, DDS. I also had it look for "," because most people put , if name was backward. I do not have the code handy But looping and writing to array seemed the way to go.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi again, heres an example of some formulas to join the christian names and obtain the surname once you have separated the names into columns. The following presumes you only have 4 columns...
111Mark.xls
ABCDEFG
1Name1Name2Name3Name4ChristianNamesSurname
2FREDSMITHFREDSMITH
3ALEXANDERJONESALEXANDERJONES
4AMYNGAMYNG
5SEBESTIANMICHAELADAMMcDONALDSEBESTIANMICHAELADAMMcDONALD
Sheet1
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

The following assumes there is always a prefix. It could be adjusted to remove such an assumption:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> StringManip()
<SPAN style="color:darkblue">Dim</SPAN> y <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>
<SPAN style="color:darkblue">With</SPAN> [A2]
    y = Evaluate("{""" & Application.Substitute(.Value, _
        ChrW$(32), """,""") & """}")
    .Item(, 2) = y(2)
    <SPAN style="color:darkblue">If</SPAN> y(UBound(y)) = "OR" <SPAN style="color:darkblue">Then</SPAN>
        .Item(, 3) = y(UBound(y) - 1)
        <SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(y) = 5 <SPAN style="color:darkblue">Then</SPAN>
            .Item(, 2) = y(2) & ChrW$(32) & y(3)
          <SPAN style="color:darkblue">ElseIf</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(y) = 4 <SPAN style="color:darkblue">Then</SPAN> .Item(, 2) = y(2)
          <SPAN style="color:darkblue">ElseIf</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(y) = 3 <SPAN style="color:darkblue">Then</SPAN> .Item(, 2) = y(1)
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
        Else:
            .Item(, 3) = y(UBound(y))
            <SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(y) = 4 <SPAN style="color:darkblue">Then</SPAN>
                .Item(, 2) = y(2) & ChrW$(32) & y(3)
              <SPAN style="color:darkblue">ElseIf</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(y) = 3 <SPAN style="color:darkblue">Then</SPAN> .Item(, 2) = y(2)
            <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi Nate nice code. Wont you need to loop through the cells?

eg
Code:
Sub StringManip()
Dim y As Variant, Rng As Range, c As Variant
Set Rng = Range("a1:a" & Range("a65536").End(xlUp).Row)

For Each c In Rng
With c
    y = Evaluate("{""" & Application.Substitute(.Value, _
        ChrW$(32), """,""") & """}")
    .Item(, 2) = y(2)
    If y(UBound(y)) = "OR" Then
        .Item(, 3) = y(UBound(y) - 1)
        If UBound(y) = 5 Then
            .Item(, 2) = y(2) & ChrW$(32) & y(3)
          ElseIf UBound(y) = 4 Then .Item(, 2) = y(2)
          ElseIf UBound(y) = 3 Then .Item(, 2) = y(1)
        End If
        Else:
            .Item(, 3) = y(UBound(y))
            If UBound(y) = 4 Then
                .Item(, 2) = y(2) & ChrW$(32) & y(3)
              ElseIf UBound(y) = 3 Then .Item(, 2) = y(2)
            End If
    End If
End With
Next c
End Sub

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

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
I just read through the quandary in more detail. The code is not robust enough, it could be adjusted, but first, here's a W.F. approach:
Book1
ABCD
1MISSCARMENANDREABAKSORCARMENANDREABAKS
2MISSCARMENANDREABAKSCARMENANDREABAKS
3MRFREDSMITHFREDSMITH
4ALEXANDERJONESORALEXANDERJONES
5MISSAMYNGAMYNG
6SEBESTIANMICHAELADAMMcDONALDSEBESTIANMICHAELADAMMcDONALD
7SEBESTIANMICHAELADAMMcDONALDJrSEBESTIANMICHAELADAMMcDONALDJr
Sheet7


If the functions bonk out they're:

Code:
=TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{"MISS","MR","MRS"},"")))),"OR",""),C1,""))

=RIGHT(SUBSTITUTE(A1," OR",""),LEN(SUBSTITUTE(A1," OR",""))-FIND(CHAR(1),SUBSTITUTE(SUBSTITUTE(A1," OR","")," ",CHAR(1),LEN(SUBSTITUTE(A1," OR",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1," OR","")," ",""))-MIN(SUMPRODUCT((LEN(SUBSTITUTE(A1," OR",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1," OR",""),{"Jr","III","IV","V","VI"},"")))),1))))
Perhaps this is good enough. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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
Top