Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine format type from strings
Does anyone know if there is an easy or automated way to take a format string
for a numeric cell and determine if it's an Int, Float (with num decimals), Date, or Time? Right now we are using a large switch, but there are always new formats that fall through and end up with the wrong type. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine format type from strings
Hi ALV,
Not quite sure what you mean. A numeric cell has a value, say 39113.542130787 The Numberformat for this cell only determins how this number is to be displayed on the sheet, but it does not change the value itself. The format is related to the meaning of the value. My example could mena today's date and time, or my bank balance (not really) and I would format it accordingly. It appears you want to go the other way. You expect the format of a cell to indicate the meaning of the value, which could work (depending on the source of the data) but only if the few standard formats are being used. As soon as a cell is formatted with a Custom-format, which by definition is flexible, there will always be new formats. Not sure what you mean by integer (no decimals displayed?) or float (scientific?). Do you mean the type of a variable? This could be interger. If so, see vartype in VBA help. -- Gerd "ALV" wrote: Does anyone know if there is an easy or automated way to take a format string for a numeric cell and determine if it's an Int, Float (with num decimals), Date, or Time? Right now we are using a large switch, but there are always new formats that fall through and end up with the wrong type. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine format type from strings
Number as stored as doubles in Excel. How they are display is determined by
the NumberFormat property. Not sure what you are after, but maybe one/some of these will be useful. Range("A1").Value = 123 Debug.Print VarType(Range("A1").Value) Debug.Print TypeName(Range("A1").Value) Debug.Print Range("A1").Text Debug.Print Range("A1").NumberFormat NickHK "ALV" wrote in message ... Does anyone know if there is an easy or automated way to take a format string for a numeric cell and determine if it's an Int, Float (with num decimals), Date, or Time? Right now we are using a large switch, but there are always new formats that fall through and end up with the wrong type. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine format type from strings
I should have been more specific...we are converting data to export to
another app which has its own set of data types. So we want to determine if the Excel user intends for each cell to show as a Time, Date, Int, Float, Dollar, or Percent. I was wondering if you can take a FormatString and easily determine if it is one of those. "NickHK" wrote: Number as stored as doubles in Excel. How they are display is determined by the NumberFormat property. Not sure what you are after, but maybe one/some of these will be useful. Range("A1").Value = 123 Debug.Print VarType(Range("A1").Value) Debug.Print TypeName(Range("A1").Value) Debug.Print Range("A1").Text Debug.Print Range("A1").NumberFormat NickHK "ALV" wrote in message ... Does anyone know if there is an easy or automated way to take a format string for a numeric cell and determine if it's an Int, Float (with num decimals), Date, or Time? Right now we are using a large switch, but there are always new formats that fall through and end up with the wrong type. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a row filled with strings to determine cell value | Excel Discussion (Misc queries) | |||
Routine with fuzzy logic to determine the relative comparison of two strings? | Excel Worksheet Functions | |||
Determine file type | Excel Programming | |||
Type mismatch error problem when dealing with Strings | Excel Programming | |||
Help - Type mismatch when running loop with strings from arrays | Excel Programming |