Problems with the Right & Find functions
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.
|