Forum Home
Press F1
Thread ID: 43934	2004-04-01 06:34:00	Excel function help	Mike (15)	Press F1

Post ID	Timestamp	Content	User
226482	2004-04-01 06:34:00	Hi, I have a value in a cell that looks something like "1234/567". I need to grab the numbers to the left of the slash ("/"). I can do this fine using this function: =LEFT(A1,(SEARCH("/",A1,1)-1)) However, if the value looks more like "5/5" Excel likes to convert it into a date like "5-May", and so I get an "#VALUE!" error in my function cell. If I change the cell format to something else (like General or Text) I get the date's numerical value (something like 38112), which isn't what I want (seeing as the value wasn't a date to start with). Is there a way to tell Excel to not automatically assume values that look like dates are dates? To make things (possibly) more complicated, the values of the cells are coming from an external (eg I can't modify it) database, so I can't alter the cell type or anything like that beforehand (if that was a possible solution). I also can't just set the cell format to something beforehand either, as when the data is refreshed from the DB the cell formats are lost anyway. Is there anything I can add just to my function to tell it that its not a date to start with? Or is there some other way of telling Excel that it's not a date? Cheers, Mike.	Mike (15)
226483	2004-04-01 06:37:00	PS I'm using Excel 2003. Unsetting the option "Enable autocomplete for cell values" doesn't have any effect. Mike.	Mike (15)
226484	2004-04-01 07:10:00	If "1234/567" and "5/5" appear on the screen as shown - they are text not values. If you are entering the data, format the column as text first. Your formula certainly works for both examples as text entries here in Excel 2002. Russell	Russell D (18)
226485	2004-04-01 20:19:00	> If "1234/567" and "5/5" appear on the screen as shown > - they are text not values. As I said, "5/5" is converted by Excel to be "5-May". The only way I have been able to get Excel to display "5/5" as that is to put a quote mark before it (to tell Excel it is a text entry), however this is not possible due to the data coming out of an external database. If Excel would display it as "5/5" I would not have any problems. Mike.	Mike (15)
226486	2004-04-01 22:30:00	Unless you import the data from the database into a cell which has already been formatted as text it is not going to work as you want.	Russell D (18)
226487	2004-04-01 22:49:00	try formating cell a1, ...format...cells..text, 5/5 will appear in the cell and not be converted into a date	jeep (1673)
226488	2004-04-01 23:49:00	Mike, Excel is pretty obdurate about formatting its dates as it likes. However, consider the following as a possible soultion to your problem: Your entry in (say A1) of 5/5 will format to a date as you have found. In B1 put =day(A1), which should give you 5 (although you may have to format B1 as 'general". If your entry in A1 is not a number that excel can format as a date you should get an error in B1. So you should be able to pick up what you want from either the A or B columns. I'm sure you can invent an efficient method, for doing this.	rugila (214)
226489	2004-04-02 00:54:00	This is a follow on from RUGILA's solution, but does it all in one step: =IF(ISERROR(DAY(A1)),LEFT(A1,(SEARCH("/",A1,1)-1)),DAY(A1)) This negates the need to format the raw data from your database, and handles both data and non dates. Hope this helps?	Marlboro (4607)
226490	2004-04-02 01:27:00	Thanks Marlboro - helpful suggestion. One further point, if you have something like 9/45, Excel is likely to convert this to 1 Sept 1945, so the =day() would give 1 instead of 9. There's (nearly always) a workaround however, and if this sort of thing bothers your results you might include somewhere an IF statement like: =if(year(A1)<> 2004, month(A1),day(A1)) or etc. It should be easy to sort out any other bugs on similar lines.	rugila (214)
226491	2004-04-04 22:27:00	Thanks everyone for your replies... I've been away for the weekend, so I'll read through them all properly, and try them out, when I get home from work this evening. :) Mike.	Mike (15)
1 2