Determining Cell Types in Excel - exceptions

[Tim Childs]

When data is imported into Excel from a separate computer
system, the amounts are held in quotes e.g.
="123.67"
When this is paste-valued, it becomes a label, NOT a value
in Excel. Pressing {F2} and return will convert it to a
label.

I have devised some code to test for data types but it
works on the table of data AFTER it has been transferred
to an array.

My question is whether the process of transferring it to
an array inevitably leads to the loss of being able to
tell whether the info is a label rather than a value.
(Disregarding explicitly storing the "type" info for each
cell).

This is long enough but maybe not very clear. Happy to
post back to clarify.

Thanks in advance

Tim

 

[Tom Ogilvy]

your formula produces the String "123.67", so it would seem consistent to
see it as text when you paste special.

if the formula were
=123.67

then if I do a paste special, it is a number.

 

[Tim Childs]

Tom

it is consistent, yes...but I know it is a number by
looking at it. But it is odd in that it is impossible to
produce this label-number with direct entry: Excel
automatically "processes" the string into a true value, if
you enter it from the keyboard.

I guess I hoped someone would come back with a formula of
the type "isnumeric" or some such thing that I may not
have come across.

THANKS

Tim

 

[Tom Ogilvy]

It wasn't terribly obvious what you question was/is. If you want to know
whether the value in a cell is stored as text or number, you can use the
isText and IsNumber worksheet functions

=isNumber(A1) will tell you how it is stored. (likewise istext)

You can select the whole range and do
Edit=>GoTo=>Special and select Constants and Text or Constants and Numbers
(after you paste special) to see which cells are being interpreted as
numbers and which as text.

So if determining how the information is stored in excel, then there are two
methods.

 

[Tim Childs]

Tom

thanks for bearing with me on this.

the twist is that the data has been transferred to variant array and I am
checking whether it is possible to determine whether these functions work in
the same way once in the array: I realise they work but the contents of the
cell and the array contents are not necessarily interchangeable in this
context. To use an (imperfect) analogy isn't it something like the
difference between "37148" and the equivalent date-number "14-Sep-01" i.e.
the number label does not exist in the same way in the array as in the
workbook's cell.

any help/comment/feedback gratefully received.

Tim

 

[Tom Ogilvy]

No, the analogy isn't equivalent. The value in the cell is 37148. the
other is formatting. In this case, the difference is whether the value in
the cell is "1234" or 1234.

When you pick it up in an array directly, what is stored in the cell is
picked up - so "1234" stays "1234" and 1234 stays 1234.

Sub Tester5()
varr = Range("Data").Value
For i = LBound(varr, 1) To UBound(varr, 1)
sStr = i & " "
For j = LBound(varr, 2) To UBound(varr, 2)
If Application.IsNumber(varr(i, j)) Then
sStr = sStr & "True, "
Else
sStr = sStr & "False, "
End If
Next
Debug.Print sStr
Next
End Sub

demonstrates this.

half my cells were numbers stored as strings, the other half were numbers
stored as numbers. The array reflected this as well.

All that said, in code, if you write code that uses it as a number, then
more than likely, it will be implicitly converted.