I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

The formula your looking for is =TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1,1)))

"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

try:

=MID(A1,FIND("-",A1)+1,255)

"beginner here" wrote:

I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

or ...

=TRIM(RIGHT(A2,LEN(A2)-FIND("-",A2,1)))

"beginner here" wrote:

I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

Use

=MID(TRIM(A1),FIND("-",TRIM(A1))+1,1024)

the RIGHT function does not work like that if you intend to return what's to
the right of the first hyphen

you can use RIGHT, but it is easier to use MID


=RIGHT(TRIM(A1),TRIM(LEN(A1))-FIND("-",TRIM(A1)))



--
Regards,

Peo Sjoblom


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

beginner here wrote:
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

Try looking at Tools/Formula auditing/Evaluate Formula. Its a big help
to see what Excel does with your formula.

This seems to work better, but there are tons of other ways.

=TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1,1)))

Beege

If you must remove all the spaces with trim you can change the formula to
=RIGHT(TRIM(A3),LEN(TRIM(A3))-FIND("-",TRIM(A3)))

"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

Thanks everybody. Finally seeing complete names, instead of me having to
adjust each cell.

"Dave Thomas" wrote:

The formula your looking for is =TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1,1)))

"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

What Dave has given out, seems to work in all cases in my workbook.

But since I am trying to learn, is to much to ask about your function. I
understand the first part of it, up to the "A1&. From then on I lose you.

Steve

"T. Valko" wrote:

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases is
just the complete second name after the hypen.

That is a way to fend off empty cells or cells that don't have any hyphens
or else you will get value errors if the hyphen is not found

It is more efficient to use the mid and find than to use right len and find


--
Regards,

Peo Sjoblom



"beginner here" wrote in message
...
What Dave has given out, seems to work in all cases in my workbook.

But since I am trying to learn, is to much to ask about your function. I
understand the first part of it, up to the "A1&. From then on I lose you.

Steve

"T. Valko" wrote:

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases
is
just the complete second name after the hypen.

=MID(A1,FIND("-",A1&"-")+1,255)

Try entering this in A1 and note the difference in my formula and Dave's:

A1 = Bill

Or, clear cell A1 and note the difference.

Here's how my formula works:

If A1 = Bill-Bob

I am concatenating a "-" to the end of the entry in A1 so that if the cell
does not contain a "-" the formula won't return an error.

In the FIND function this is how that looks:

FIND("-","Bill-Bob-")

FIND will return the position of the *first* instance of "-".

If A1 was just plain old Bill, then it would look like this:

FIND("-","Bill-")

So the formula is returning *everything* to the immediate right of the
*first* instance of "-".

If A1 = Bill there is no "-" so the formula returns an empty string which in
effect leaves the cell blank. Basically, this is used as an error trap. It's
easier than using:

=IF(ISNUMBER(FIND("-",A1)),MID(A1,FIND("-",A1)+1,255),"")

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
What Dave has given out, seems to work in all cases in my workbook.

But since I am trying to learn, is to much to ask about your function. I
understand the first part of it, up to the "A1&. From then on I lose you.

Steve

"T. Valko" wrote:

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases
is
just the complete second name after the hypen.

I did have one cell that only had one name in it, and I got the value error.
I had thought that I just might force the hyphen at the front of the name to
make the formula work. Now I understand why I got the error.

Thanks for explaning it to me.

Steve

"Peo Sjoblom" wrote:

That is a way to fend off empty cells or cells that don't have any hyphens
or else you will get value errors if the hyphen is not found

It is more efficient to use the mid and find than to use right len and find


--
Regards,

Peo Sjoblom



"beginner here" wrote in message
...
What Dave has given out, seems to work in all cases in my workbook.

But since I am trying to learn, is to much to ask about your function. I
understand the first part of it, up to the "A1&. From then on I lose you.

Steve

"T. Valko" wrote:

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases
is
just the complete second name after the hypen.

Thanks for the reply. My understanding has just increased by you and
everybody else's help this afternoon.

Thanks again.

Steve

"T. Valko" wrote:

=MID(A1,FIND("-",A1&"-")+1,255)

Try entering this in A1 and note the difference in my formula and Dave's:

A1 = Bill

Or, clear cell A1 and note the difference.

Here's how my formula works:

If A1 = Bill-Bob

I am concatenating a "-" to the end of the entry in A1 so that if the cell
does not contain a "-" the formula won't return an error.

In the FIND function this is how that looks:

FIND("-","Bill-Bob-")

FIND will return the position of the *first* instance of "-".

If A1 was just plain old Bill, then it would look like this:

FIND("-","Bill-")

So the formula is returning *everything* to the immediate right of the
*first* instance of "-".

If A1 = Bill there is no "-" so the formula returns an empty string which in
effect leaves the cell blank. Basically, this is used as an error trap. It's
easier than using:

=IF(ISNUMBER(FIND("-",A1)),MID(A1,FIND("-",A1)+1,255),"")

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
What Dave has given out, seems to work in all cases in my workbook.

But since I am trying to learn, is to much to ask about your function. I
understand the first part of it, up to the "A1&. From then on I lose you.

Steve

"T. Valko" wrote:

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
I am having probems with combining both the right and the find function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all cases
is
just the complete second name after the hypen.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
Thanks for the reply. My understanding has just increased by you and
everybody else's help this afternoon.

Thanks again.

Steve

"T. Valko" wrote:

=MID(A1,FIND("-",A1&"-")+1,255)

Try entering this in A1 and note the difference in my formula and Dave's:

A1 = Bill

Or, clear cell A1 and note the difference.

Here's how my formula works:

If A1 = Bill-Bob

I am concatenating a "-" to the end of the entry in A1 so that if the
cell
does not contain a "-" the formula won't return an error.

In the FIND function this is how that looks:

FIND("-","Bill-Bob-")

FIND will return the position of the *first* instance of "-".

If A1 was just plain old Bill, then it would look like this:

FIND("-","Bill-")

So the formula is returning *everything* to the immediate right of the
*first* instance of "-".

If A1 = Bill there is no "-" so the formula returns an empty string which
in
effect leaves the cell blank. Basically, this is used as an error trap.
It's
easier than using:

=IF(ISNUMBER(FIND("-",A1)),MID(A1,FIND("-",A1)+1,255),"")

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
What Dave has given out, seems to work in all cases in my workbook.

But since I am trying to learn, is to much to ask about your function.
I
understand the first part of it, up to the "A1&. From then on I lose
you.

Steve

"T. Valko" wrote:

The "problem" is that Excel's logic is built to go from left to right.

Try it like this:

=MID(A1,FIND("-",A1&"-")+1,255)

--
Biff
Microsoft Excel MVP


"beginner here" wrote in
message
...
I am having probems with combining both the right and the find
function
command.

My formula is: =TRIM(RIGHT(A1,FIND("-",A1,1)-1))

Say in cell A1 to A3 I have the following combination of names.
A1=Steve-Caldog, A2=Tom-Jones,A3=April-Showers

My results are the following in B1 through B3:
B1=aldog
B2=nes
B3=owers

Now the formula I have kepted the same for all three examples above.

What am I doing wrong? In my situation, I can have any number of
combinations. I would like one formula so that I can see in all
cases
is
just the complete second name after the hypen.