Previous Topic Next Topic

VBA/Macro: Removing titles

Solved/Closed
Austin - Dec 3, 2009 at 02:13 AM
 Austin - Dec 10, 2009 at 08:41 PM
Hello,

I need to remove Mr/Mrs/Miss preceeding the names in one cell and copy them to another cell. Any idea? Your advice is much appreciated.

ie.
In C2: Miss Jane Smith
Should be, in C3: Jane Smith

Austin

3 responses

Answer 1 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 3, 2009 at 05:38 AM
try this formula 

=MID(C2,SEARCH(" ",C2)+1,LEN(C2)-SEARCH(" ",C2)+1)


this will work whatever be the tile in C2 Mr,Mrs or Miss etc. The logic is find the first blank(that is after the title) and use that position in the mid function.

type in C2
Mr john smith or Miss Gabriel smith
and see what happen in the formula cell
1
Austin
Dec 3, 2009 at 08:50 PM
Hello ventkat1926!

It works! Thank you. Another question though, what if I need the Jane Smith to show up on another sheet?


Austin
0
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 4, 2009 at 05:16 AM
you include sheets name before with an exclamation mark.

suppose Mr john smith is in sheet 1 cell C2

then in any other sheet and in any empty copy this formula 

=MID(Sheet1!C2,SEARCH(" ",Sheet1!C2)+1,LEN(Sheet1!C2)-SEARCH(" ",Sheet1!C2)+1)


experiment with this formula
1
Austin
Dec 7, 2009 at 03:28 AM
Am trying this. Will get back to you. Thanks venkat1926!
0
Austin
Dec 8, 2009 at 08:27 PM
Hi!

I got syntax error when I used it. Here's the entire code I'm working on:

Dim counter As Integer
Dim row As Integer
Dim strFormula As String

counter = 2
row = 2

Do Until IsEmpty(Sheets("ORG CHART").Range("A" & row))

If Sheets("ORG CHART").Range("E" & row) = "PERMANENT" Then
counter = counter + 1
Sheets("MASTER DATA").rows(counter).Columns("A").Value = Sheets("ORG CHART").rows(row).Columns("I").Value
Sheets("MASTER DATA").rows(counter).Columns("C").Value = Sheets("ORG CHART").rows(row).Columns("P").Value
Sheets("MASTER DATA").rows(counter).Columns("D").Value = Sheets("ORG CHART").rows(row).Columns("C").Value
Sheets("MASTER DATA").rows(counter).Columns("E").Value = Sheets("ORG CHART").rows(row).Columns("U").Value
Sheets("MASTER DATA").rows(counter).Columns("F").Value = Sheets("ORG CHART").rows(row).Columns("V").Value
Sheets("MASTER DATA").rows(counter).Columns("G").Value = Sheets("ORG CHART").rows(row).Columns("J").Value

strFormula = "=MID(Sheets("ORG CHART")!Range("K" & row),SEARCH(" ",Sheets("ORG CHART")!Range("K" & row)+1,LEN(Sheets("ORG CHART")!Range("K" & row))-SEARCH(" ",Sheets("ORG CHART")!Range("K" & row)+1)

Sheets("MASTER DATA").rows(counter).Columns("B").Value = strFormula

End If
row = row + 1
Loop

Help!
0
Austin > Austin
Dec 8, 2009 at 08:30 PM
Oh wait. I just forgot to place closing quotations in the formula.

But now I get a compile error (expected: end of statement) and the the ORG after ="MID(Sheets(" gets highlighted. Why is this?
0
Answer 3 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 9, 2009 at 05:09 AM
I have given a formula not a vba macro. it is little complex to use a formula with variable in vba

send a small extract of your date and what you want to be doen . attach .xls file to my email address through the administrator if allowed.
0
Austin
Dec 10, 2009 at 08:41 PM
I seem to be permitted from seeing your email. Can you give me your email via this thread?

Thanks a lot!
0

Similar discussions

Create folder in desktop with vba, if exist, ignore
viriato74 -
praveen -

6 responses