| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use FIND for / or \ or -
appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
Try this:
=LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1) And copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Bert" wrote in message ... appy New Year all, I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
Instead of using:
=FIND() use: =IF(ISERROR(FIND("-",A1,1)),0,FIND("-",A1,1))+IF(ISERROR(FIND("\",A1,1)),0,FIND("\",A1, 1))+IF(ISERROR(FIND("/",A1,1)),0,FIND("/",A1,1)) -- Gary''s Student - gsnu200762 "Bert" wrote: appy New Year all, I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
Playing off of RD's really nifty solution, this one will return a blank if
there is no occurance of \, /, or- in the cells =IF(LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1)=A1,"",LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1)) Vaya con Dios, Chuck, CABGx3 "RagDyeR" wrote: Try this: =LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1) And copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Bert" wrote in message ... appy New Year all, I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
Another way...
=LEFT(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (A1,"\","^"),"/","^"),"-","^"))-1) "Bert" wrote: appy New Year all, I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
Thanks for all your solutions as I see a use for them in other work
Bert "Teethless mama" wrote: Another way... =LEFT(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (A1,"\","^"),"/","^"),"-","^"))-1) "Bert" wrote: appy New Year all, I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
Why not just pick one of the search characters and then do only two
substitutions (one for each of the other characters)... =LEFT(A1,FIND("\",SUBSTITUTE(SUBSTITUTE(A1,"/","\"),"-","\"))-1) Rick "Teethless mama" wrote in message ... Another way... =LEFT(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (A1,"\","^"),"/","^"),"-","^"))-1) "Bert" wrote: appy New Year all, I want to use the FIND function with LEFT to extract the numbers left of a marker which could be / or \ or - I can only see how to use FIND for one character how to use for multiple possibilites? (so when FIND reaches / or \ or - it returns the position) Thanks for any suggestions Bert |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use FIND for / or \ or -
CLR wrote...
Playing off of RD's really nifty solution, this one will return a blank if there is no occurance of \, /, or- in the cells =IF(LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1)=A1, "",LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1)) .... No need for multiple FIND calls. =SUBSTITUTE(LEFT(A1,MIN(FIND({"/","\","-"},A1&"/\-"))-1),A1,"") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
| where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
| Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
| 'find' somtimes can't find numbers. I folowd the 'help' instructi. | Excel Worksheet Functions | |||
| How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) | |||
Linear Mode
