PDA

View Full Version : Excel function help



Mike
01-04-2004, 06:34 PM
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
01-04-2004, 06:37 PM
PS I'm using Excel 2003. Unsetting the option "Enable autocomplete for cell values" doesn't have any effect.

Mike.

Russell D
01-04-2004, 07:10 PM
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

Mike
02-04-2004, 08:19 AM
> 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.

Russell D
02-04-2004, 10:30 AM
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.

jeep
02-04-2004, 10:49 AM
try formating cell a1, ...format...cells..text, 5/5 will appear in the cell and not be converted into a date

rugila
02-04-2004, 11:49 AM
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.

Marlboro
02-04-2004, 12:54 PM
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?

rugila
02-04-2004, 01:27 PM
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.

Mike
05-04-2004, 10:27 AM
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.

rugila
05-04-2004, 11:10 AM
OK.
But just for completeness, a couple of further things:
1. I don't know how to prevent Excel from formatting things like 25/12 as Xmas day etc. I don't think it can be done. Even going into control panel and changing the system date separator makes no difference.
2. The approach I suggested is to recover the numbers you want AFTER Excel has automatically date-formatted them.
3. As far as I can tell, my above suggestions all work fine, EXCEPT that you may have to watch out for the special case where the number after the slash is 2004. That one's not hard to resolve tho' and I'm sure you won't have any trouble in sorting it out if need be - just find out what Excel actually does in this case.