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,

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,

Thank you Jim. Worked like a charm. Tom, thank you for your input. But I have
to go with Jim's answer because I am doing this for someone else and need to
work up a bill ;o).

Thanks guys..have a great day.
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,

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,

Now you are in trouble. This is why databases store names in seperate parts.
You can either use Tom's suggestion or I can write you a VBA function that
will find the last space... You will have to include it in a module in your
spreadsheet.

Let me know...

"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,

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,

Thank you Jim. I am a newbie here and so how do I associate this module with
column H? Do I create a macro on the specific sheet or the workbook? I truly
appreciate your help here.

Thanks Tom for your solution. Unfortunately, I keep getting an error (#Value)

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

I changed the Column to H2 throughout to match my sheet but keep getting an
error. Also, could you please explain the use of the ~ in the formula?

Thanks you so much,
VJ

"Jim Thomlinson" wrote:

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,

Ok..after looking around, I have finally managed to associate the module with
the column. But I keep getting the #Value error....can't figure out
why..help!!!!!

VJ

"Jim Thomlinson" wrote:

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,

Ok, Now I get numbers like 13, 10, 9, 11 etc instead of the word. This is the
number of characters from the first space in the entry. Does this help you
figure out the problem??

Thanks again,
VJ

"Jim Thomlinson" wrote:

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,

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,

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,

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,