There is a word wrap problem - you have missed a space in the formula

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)
-LEN(SUBSTITUTE(A1," ",""))))+1,99)

for you
=MID(H2,FIND("~",SUBSTITUTE(H2," ","~",LEN(H2)
-LEN(SUBSTITUTE(H2," ",""))))+1,99)

in the very last part, you had "","")))) and it should be " ",""))))

Plus you aren't using the whole formula as shown above.

--
Regards,
Tom Ogilvy



I have broken it in half and you will see the where you have
"VJ" wrote in message
...
Ohh.. I see what you are doing. You substitute all spaces by ~ and then
locate the last ~. I evaluated the expression and the
SUBSTITUTE(H2," ","~",LEN(H2)-LEN(SUBSTITUTE(H2,"",""))) causes the error
and I can't figure out why...

VJ

"Tom Ogilvy" wrote:

This formula gets the last word in a string (1 past the last space until
the
End)

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,99)

Of course you will problems with names like Von Richtoffen and de
Bruin or
O' Malley if they put in a space.

But it should give you a 90% solution.

--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
This isn't exactly your situation, but it might give you some ideas:

http://www.cpearson.com/excel/FirstLast.htm

--
Regards,
Tom Ogilvy

"VJ" wrote in message
...
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,