Copy cells without splitting column

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,

copy the whole column and paste it two columns to the right

then select the original column and do Data=>Text to columns and select
delimited and on the next dialog, select comma as the delimiter.

Now delete the column of State Zip that you don't want.

--
Regards,
Tom Ogilvy

"VJ" <[email protected]> wrote in message
news:[email protected]...
> 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,

This isn't exactly your situation, but it might give you some ideas:

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

--
Regards,
Tom Ogilvy

"VJ" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> This isn't exactly your situation, but it might give you some ideas:
>
> http://www.cpearson.com/excel/FirstLast.htm
>
> --
> Regards,
> Tom Ogilvy
>
> "VJ" <[email protected]> wrote in message
> news:[email protected]...
> > 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,

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" <[email protected]> wrote in message
> news:[email protected]...
> > This isn't exactly your situation, but it might give you some ideas:
> >
> > http://www.cpearson.com/excel/FirstLast.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "VJ" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > This isn't exactly your situation, but it might give you some ideas:
> > >
> > > http://www.cpearson.com/excel/FirstLast.htm
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "VJ" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 so much Tom. It worked like a charm. The incomplete formula I
listed was to show the section of the actual formula that evaluated to a
#Value error. The missing space was giving me the error. Now it works fine..

Thank you guys a whole lot once again and have a wonderful day,
VJ

"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This isn't exactly your situation, but it might give you some ideas:
> > > >
> > > > http://www.cpearson.com/excel/FirstLast.htm
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "VJ" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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,
> > > >
> > > >
> > >
> > >
> > >
>
>
>