Click here to Skip to main content
15,663,728 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have data that I import to an Excel spreadsheet. Age is reported as "GENERAL" with the green indicator noting that the number is stored as a text. I would like to convert the entire column of ages to a NUMBER format. Should be easy!

Manual attempts to reformat as a NUMBER fails with the green indicator still appearing. The number still behaves as text. There is no apostrophe preceding the number.

Attempts at reformatting as CUSTOM ###0 also fails to remove the text formatting.

Using the Green indicator box and converting text to number does work, but I want to fix the entire column via VBA code, in order to perform operations on many rows of data.

This VBA code has no effect:

Range("H2:H" & Last_Row).Select
Selection.NumberFormat = "#,##0"

Any ideas how to do this? Does the report change the sheet to where the text is somehow sticky?


What I have tried:

Range("H2:H" & Last_Row).Select
        Selection.NumberFormat = "#,##0"
Updated 29-Jul-22 9:33am

1 solution

Since you're using VBA, you can iterate over every value in the column and then simply convert the string to a int.

See the following for converting the value :[^]

check out this code (in an image snapshot) [^] that shows how to iterate over rows & even shows how to format each cell. It may help.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900