1

I have a table of numbers that are all left aligned (i.e. Excel recognizes them as text)

I run a VBA script on all cells:

cell.value = cell.Value * 1

This right aligns all of them and Excel recognized them as numbers except for decimals (e.g. 3.14 does not work while 314 works). I also run a find and replace script, where the search is for space (" ") and replace it with a blank(""), so this should get rid of atleast the common space.

Further clues: If i perform the =Value(A1) formula in Excel, Excel will recognize even the decimals as a number. If I run Workbookfunction.value(A1) Excel will not recognize as a number.

So the problem seems ro be related to VBA (?) and decimals. Any solutions?

I now ran the following after comments here:

For Each cell In rng
Dim vNumber As Double
On Error Resume Next
'Remove space
cell.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

'Remove comma
cell.Replace What:=",", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False

'Check if empty, if it is: Do nothing
If IsEmpty(cell) = True Then

Else
vNumber = CDbl(cell.Value)
cell.Value = vNumber
End If

'Check if numeric
If IsNumeric(cell) = True Then
cell.Interior.Color = RGB(0, 254, 0)
cell.Interior.TintAndShade = 0.8

Else
cell.Interior.Color = RGB(100, 0, 0)
cell.Interior.TintAndShade = 0.8

End If
Next cell

The result is the following Before and After (where one is with Double and other with Variant. Somehow its writing over cells that are not decimals...

enter image description here

enter image description here

Improve this question
5
  • I just tried to edit your question to filter out the irrelevant - and failed. The relevant information just isn't there. Your first line seems to say that you have text. But I miss the information that cells contain only numbers. Your question is how to recognize numeric values and that implies that there are some which aren't numeric. All the rest of your post (except for the first line) indicates that you let your code write the correct result to a cell whose format you don't know how to set. So, 3.14 is displayed as 3 and you blame the code instead of the cell format. Commented Mar 15, 2020 at 11:13
  • What I would like to know is, if the "Before" cell is a text (left-aligned) "1.5" what should be the output. Instead of the "13.9" you input, what do you want to come out? Removing commas may be useful if your system's decimal separator is a point and the data have a comma instead. You don't say. Removing spaces should not be useful, nor do you show where you original data have spaces. Converting 13.6 to 5 is a sign of faulty code, not of faulty VBA. Please make it easier for us. Just tell us what you want. Commented Mar 15, 2020 at 11:18
  • "if the "Before" cell is a text (left-aligned) "1.5" what should be the output." "1.5" + formatted so Excel reconizes it's a number. At the outset Excel does not recognize it as a number. Instead of the "13.9" you input, what do you want to come out? "13.9". Same as above. "Converting 13.6 to 5 is a sign of faulty code, not of faulty VBA. " Yes, I agree. That's why I'm asking for help :) Commented Mar 15, 2020 at 11:45
  • My revised code presumes that the target cell's cell format is "General". When you change this format the display will change, too. Please read up about cell formats to gain better control but use "General" until you do. Right-click a cell, select Format Cells > Numbers and look at the formats available there. Google for instructions to set custom cell formats if you need them. Commented Mar 15, 2020 at 12:07
  • I'm familiar with cell formatting. Goal is to automate this data validation phase as much as possible. Manually clicking through a dropdown menu doesn't fit the goal :) Commented Mar 15, 2020 at 13:45

3 Answers 3

Reset to default
1

You need to convert the value of the cell to a double. For example:

Dim myDouble As Double
myDouble = CDbl(Range("A1").Value)
Debug.Print myDouble
myDouble = myDouble + 1
Debug.Print myDouble

Lines 3 to 5 are just to demonstrate that it gets recognised as a decimal.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you! Getting some weird results.... For Each cell In rng Dim vNumber As Double If IsEmpty(cell) = True Then Else vNumber = CDbl(cell.Value) cell.Value = vNumber End If It runs through everything but the integers write over all the decimals below it. I'll try to post a screenshot.
If you've got some extra code to show, you can edit your original post to show the new code. That way you can insert it as "code" which makes it easier to read :)
1

Did you try conversion?

Sub test()

    Dim rng As Range, cell As Range

    With ThisWorkbook.Worksheets("Sheet1")

        Set rng = .Range("A1:A5")

        For Each cell In rng
            .Range("B" & cell.Row).Value = CDbl(cell)
        Next cell

    End With

End Sub

Results:

enter image description here

Improve this answer

1 Comment

I wonder why your's is working and mine ends up replacing the numbers containing decimals... Maybe related to the way I define my range? Also, is there a way to implement this so the data is written in the same cell?
1

Val(Cells(1,1).Value will convert a string to a number if it's numeric, to zero if it's not. "123abc" will be converted to the number 123. IsNumeric(Cells(1,1).Value) will return True if there are no non-numeric characters in the cell's string.

Incidentally, VBA's Val() function will ignore blanks. Val(123 456") will return the number 123456.

The code below will meet your updated requirements. Please try it.

Sub ConvertTextToNumbers()

    Dim Rng As Range
    Dim Cell As Range
    Dim Arr As Variant
    Dim R As Long

    With Worksheets("Sheet1")           ' modify to suit
        Set Rng = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
        Arr = Rng.Value

        For R = 1 To UBound(Arr)
            ' remove commas and spaces
            Arr(R, 1) = Val(Replace(Replace(Arr(R, 1), " ", ""), ",", ""))
        Next R

        Rng.Offset(0, 1).Value = Arr

        For Each Cell In Rng.Offset(0, 1)
            Cell.Interior.Color = IIf(Cell.Value, vbGreen, vbRed)
        Next Cell
    End With
End Sub
Improve this answer

7 Comments

Thanks, but that does not seem to fit my needs. I need blanks to stay blanks. Also, if there is something non-numeric I need it to be flagged so I can examine and correct it where needed. Can't have Excel guessing what it might be.
Please tell us what kind of data you have and what kind of output you want. Your question is somewhere hidden among your narrative and the solution you need is contained, I assure you, in my answer above. But in order to have a more precise answer you need to ask a precise question. What kind of data do you have: Text, numbers, numeric text? What kind of output do you require? Integers, double, text, numeric text, dates?
I have data that should be only numbers but can contain anything. It is also formatted as text. I want to: 1) clean out spaces and commas 2) get the formatting right so Excel can check if its numeric or not 3) flag numeric data as numeric (green) 4) flag non-numeric data as non-numeric (red)
That's nice! Easy to do. I have updated my answer. Have a great day!
Looks like Variatus has what you need. To keep blanks, just add an If before you amend Arr(R,1). You can put If Len(Arr(R, 1)) > 0 Then. This will check for blanks as the length will be zero. To keep it in the same column, just remove the .Offset(0,1) from both lines of code.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.