If you put this code in a module then you can use it to find the last
instance of a text string within a text string.

Public Function FindLast(ByVal StringToFind As String, ByVal InString As
String) As variant
FindLast = InStrRev(InString, StringToFind)

End Function


"VJ" wrote:

Ok..Now I am having a new problem and hope you don't mind helping me out.

I have a column (H2) with Names in the format:

Dr. A. Wayne Lowen
Dr. Wayne Echols
Friar. Mark Cooper
Mr. Dale Dopp

I am required to do 2 things:
1. Copy the Prefix (Mr, Dr etc in a separate column) - which I have
achieved, thanks to Jim.

2. Copy the Lastname into a separate column so I have

Dr. A. Wayne Lowen | Lowen
Dr. Wayne Echols | Echols
Friar. Mark Cooper | Cooper
Mr. Dale Dopp | Dopp

But this is what I get using: =RIGHT(H2, FIND(" ", H2)+1)

Dr. A. Wayne Lowen | Lowen
Dr. Wayne Echols | chols
Friar. Mark Cooper | k Cooper
Mr. Dale Dopp | Dopp
Mr. Alan Norton | orton

I've looked up and tried the MID and combinations of MID, LEFT, RIGHT, LEN,
FIND and still cannot seem to get the proper result or figure out whats
happenning. Could you please help me out?

Thanks a lot,
VJ

"Jim Thomlinson" wrote:

Here is a fairly stright forward formula that will work so long as there is a
comma right after the city.

=LEFT(B2, FIND(",", B2)-1)

This assumes that you wnat to split cell B2. If you wnat to do more text
manipulation look up mid, len, left, right and find in the help. Using
combinations of these you can split up most anything you want.

In VBA look up the split function...

HTH

"VJ" wrote:

Hello,

I have an excel sheet with about 250 records. I am new to Excel Programming
and was wondering how to accomplish the following.

I have a column that holds the City, State and Zip details:- | City, State
Zip |
and want to COPY the City alone and put it in a separate column without
having to split the original column. This should result in the following:-
| City | City, State Zip |

Could someone please tell me how to accomplish this?

Thank you and have a great day,