Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
finding char within a cell
I posted this a while ago and it never showed up. I apoligize if it
duplicates itself. I have a list of account numbers in column A. JOR123 1234 567US/789US PB34-7890 I need the text or numbers before the - or the / I am using the find function to get the poistion along with the right function to pull out the info. The problem is I dont know how to look for the - or the /. I can only do one or the other. Thanks for your help |
#2
|
|||
|
|||
One way:
=LEFT(A1,FIND("/",SUBSTITUTE(A1,"-","/"))-1) or if you need to take all of the number if there is no / or -: =IF(ISERR(FIND("/",SUBSTITUTE(A8,"-","/"))),A8, LEFT(A8,FIND("/",SUBSTITUTE(A8,"-","/"))-1)) In article , Jordan wrote: I posted this a while ago and it never showed up. I apoligize if it duplicates itself. I have a list of account numbers in column A. JOR123 1234 567US/789US PB34-7890 I need the text or numbers before the - or the / I am using the find function to get the poistion along with the right function to pull out the info. The problem is I dont know how to look for the - or the /. I can only do one or the other. |
#3
|
|||
|
|||
Another way...
=IF(OR(ISNUMBER(SEARCH({"/","-"},A1))),LEFT(A1,MIN(SEARCH({"/","-"},A1&"/ -"))-1),A1) Hope this helps! In article , Jordan wrote: I posted this a while ago and it never showed up. I apoligize if it duplicates itself. I have a list of account numbers in column A. JOR123 1234 567US/789US PB34-7890 I need the text or numbers before the - or the / I am using the find function to get the poistion along with the right function to pull out the info. The problem is I dont know how to look for the - or the /. I can only do one or the other. Thanks for your help |
#4
|
|||
|
|||
Domenic wrote...
Another way... =IF(OR(ISNUMBER(SEARCH({"/","-"},A1))),LEFT(A1,MIN(SEARCH({"/","-"}, A1&"/-"))-1),A1) .... If you're going to append "/" and "-" to A1, there's no need for the error trap. =LEFT(A1,MIN(FIND({"-","/"},A1&{"-","/"}))-1) |
#5
|
|||
|
|||
Ahh yes... Thanks Harlan!
In article . com, "Harlan Grove" wrote: Domenic wrote... Another way... =IF(OR(ISNUMBER(SEARCH({"/","-"},A1))),LEFT(A1,MIN(SEARCH({"/","-"}, A1&"/-"))-1),A1) ... If you're going to append "/" and "-" to A1, there's no need for the error trap. =LEFT(A1,MIN(FIND({"-","/"},A1&{"-","/"}))-1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |