Problems with the Right & Find functions

[Guest]

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.

 

[Dave Thomas]

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

 

[Guest]

try:

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

 

[Guest]

or ...

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

 

[Peo Sjoblom]

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)))

 

[Beege]

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

 

[Dave Thomas]

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

 

[Guest]

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

 

[T. Valko]

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)

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[T. Valko]

=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)

 

[Guest]

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

 

[Guest]

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

Thanks again.

Steve

 

[T. Valko]

You're welcome. Thanks for the feedback!