Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions Second Look.
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
=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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with the Right & Find functions
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having a few problems If functions and using 'or' | Excel Discussion (Misc queries) | |||
Problems with Excel functions | Excel Discussion (Misc queries) | |||
Problems with functions | Excel Worksheet Functions | |||
Problems with functions | Excel Worksheet Functions | |||
Problems with functions | Excel Worksheet Functions |