Finding char within a cell
I have a column of account numbers listed as follows:
US1234 6789 1234-12345 ABC/ABC62 What I am trying to do is to take the text and/or numbers before the "-" or the "/". I have tried using the find function which will give me the location and then I can subtract that from the length and use the right fuction. The problem I'm having is I cant figure out how to have the find function return the location whether or not it's the - or the /. Any help will be greatly appreciated. Jordan |
On Thu, 16 Jun 2005 15:20:03 -0700, Jordan
wrote: I have a column of account numbers listed as follows: US1234 6789 1234-12345 ABC/ABC62 What I am trying to do is to take the text and/or numbers before the "-" or the "/". I have tried using the find function which will give me the location and then I can subtract that from the length and use the right fuction. The problem I'm having is I cant figure out how to have the find function return the location whether or not it's the - or the /. Any help will be greatly appreciated. Jordan Try this: =IF((COUNTIF(A1,"*-*")+COUNTIF(A1,"*/*"))=0,A1, MID(A1,FIND(CHAR(1),SUBSTITUTE( SUBSTITUTE(A1,"-",CHAR(1)),"/",CHAR(1)))+1,255)) --ron |
How about using find/replace to change all the "/" into "-". Then use Data
Text to columns to separate the text into two pieces? -- Gary's Student "Jordan" wrote: I have a column of account numbers listed as follows: US1234 6789 1234-12345 ABC/ABC62 What I am trying to do is to take the text and/or numbers before the "-" or the "/". I have tried using the find function which will give me the location and then I can subtract that from the length and use the right fuction. The problem I'm having is I cant figure out how to have the find function return the location whether or not it's the - or the /. Any help will be greatly appreciated. Jordan |
All times are GMT +1. The time now is 03:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com