Change imported numbers in text format to numbers

**navialivad** · 10-02-2014, 11:31 AM

Greetings

I have a macro that imports some numbers from a file where they are formatted as text, in the new sheet they appear with the little green icon on the top left telling me that the numbers have been stored as text. Attached is a workbook showing my problem, at the top are the ones stored as text, at the bottom are ones added by the macro formatted as numbers. I need them in the same format to use in a pivot table.

Already tried simply formatting and also tried copying and pasting values

Thanks for the help

**FlameRetired** · 10-02-2014, 12:05 PM

Look at cells B2:B12. The formula I used to convert the text dates to date values is =--A2. I just copied down. The "--" in front of the A2 might be confusing. It is one of many ways to coerce the underlying numeric value in the text representation of numbers. Simply adding =0+A1 works as well as =1*A1.

Hope this helps.

Edit: I almost forgot. You can copy and paste special/values the B2:B12 cells. Then just move the entire range over to column A replacing the text.

**navialivad** · 10-02-2014, 02:30 PM

Thanks for the info, that allowed me to create the macro code I needed. In case anyone else has the question and needs the macro code:
The variable "lastrow" stores the final row in the worksheet
CS is the first empty column where I could use the information above

        
        ...
        'This section is done to convert the numbers written as text in column A to numbers
        Range("CS2").Formula = "=RC[-96]*1"
        Range("CS2").Select

        'This serves as pulling the formula down
        Selection.AutoFill Destination:=Range("CS2:CS" & lastrow), Type:=xlFillDefault

        'Calculations are set to manual, so there is the need to calculate
        Worksheets("sheet1").Calculate
        Range("CS2:CS" & lastrow).Copy
        Range("A2").PasteSpecial xlPasteValues

        'Delete extra column
        Range("CS:CS").Delete
        ...