I notice that numeric values like 123456 can be considered as numbers or non-numbers in Excel. Mixing numbers and non-numbers may result in unexpected results of = or XLOOKUP.

For instance, in the following worksheet, the formula of D3 is =ISNUMBER(C3) and the formula of D4 is =ISNUMBER(C4). Their values are not the same. Then =C3=C4 in another cell will return FALSE; =XLOOKUP(C3,C4,C4) will return #N/A.

So one solution to avoid such surprises is that I would like to convert all these numeric values from numbers to non-numbers, before applying formulas on them.

Does anyone know if it is possible to undertake this conversion by manual operations (select the range, then...)?

Does anyone know how to achieve this conversion by a subroutine in VBA (select the range, then run the VBA subroutine, then the selected range will be converted)?

CodePudding user response：

Of course you cannot compare apples to oranges, thus strings are not comparable to integers/longs/numbers. Make sure that all you compare are apples.

In a routine this would be s.th. like

Option Explicit

Sub changeFormat():

' Declare variables

Dim Number As Variant
Dim check As Boolean

'Converts the format of cells D3 and D4 to "Text"

Range("D3:D4").NumberFormat = "@"

'Assign cell to be evaluated

Number = Range("D3")

Debug.Print Number 'Prints '123'

check = WorksheetFunction.IsText(Trim(Sheets("Tabelle1").Cells(4, 3)))

Debug.Print check 'Prints True

'Converts the format of cells D3 and D4 to "Numbers"

Range("D3:D4").NumberFormat = "0.00"

'Compare Cells

If Range("D3").NumberFormat = Range("D4").NumberFormat Then Range("D5").Value = "Same Format"

End Sub

Also see the docs

CodePudding user response：

1. If you firstly write numbers in a range, let us say "C:C", formatted as General, any such a cell will return TRUE when you try =ISNUMBER(C4).

2. If you preliminary format the range as Text and after that write a number, this will be seen by Excel as a String (non-numbers, as you say...) and =ISNUMBER(C4) will return False.

3. Now, if you will try formatting the range as Text after writing the numbers these cells will not be changed in a way to make =ISNUMBER(C4) returning FALSE. In order to do that, you can use TextToColumns, as in the next example:

Private Sub testTextToCol()
 Dim sh As Worksheet, rng As Range

 Set sh = ActiveSheet
 Set rng = sh.Range("C:C")
    rng.TextToColumns Destination:=rng, FieldInfo:=Array(1, 2)
End Sub

It will make the existing =ISNUMBER(C4), initially returning TRUE, to return FALSE...