Convert text to number in Excel in VBA [duplicate]

This question already has answers here: 开发者_如何学C VBA: Convert Text to Number (11 answers) Closed 4 years ago.

For some reason Excel is converting my number into text and adding a preceding apostrophe in every cell in column E3 and F3.

I need to convert columns E3:F3 back to numbers and format them to currency. How do I do that?

A1:K2 is the header.

The code below is not working:

Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).Rows(2).Delete
wb.Sheets(1).Range("E3:F3") = Mid(wb.Sheets(1).Range("E3:F3"), 2,
                              Len(wb.Sheets(1).Range("E3:F3")) - 2)
wb.Sheets(1).Range("E3:F3").NumberFormat = "$#,##0.00"

---

If your text is only a number, the answer is simple. Multiply it by 1.
Say cell A1= '00001234 or a formula or macro returns a piece of text, multiply the answer by 1. "00001234" * 1 = 1234.
I want to extract the value of a Label or a txtBox on a VBA form.
Num = lblText * 1
Another example: If .txtLevel10 * 1 > 50 Then...etc.
Also works with some other data types "16-Jan-15" *1 = 16/1/15
Works in Excel & VBA but only if there are no other characters in the original text. Cheers

---

Assuming you want the same currency formatting you get from the toolbar, this works:

wb.Sheets(1).Range("E3:F3").Formula = wb.Sheets(1).Range("E3:F3").Value
wb.Sheets(1).Range("E3:F3").Style = "Currency"

Just using worksheet.Range() with no properties forces Excel to guess exactly which property you actually mean (this is called the "default property"), and you get inconsistent results.

---

Try:

Range("E3:F3").Style = "Currency"

---

Try highlighting that column and doing Data->Text To Columns (Excel 2003; in 2007+ Text to columns is on one of the ribbons). Click 'Delimited', then 'Next', Next again, then select General as the format. This should convert the whole column into a number.

If this works, it is easily automated. Let me know if you need code.

EDIT - you have to do one column at a time.

---

Len(wb.Sheets(1).Range("E3:F3"))

For me this just (as expected) throws an error. You'll have to process each cell individually to use your approach.

Dim c as Range
Set wb = objApp.Workbooks.Open("aFile.xls", True, False)    

With wb.Sheets(1)
   .Rows(2).Delete    
   For each c in .Range("E3:F3").cells
     c.Value = Mid(c.value, 2, Len(c.value)-2) 
     c.NumberFormat = "$#,##0.00"
   next c
End With

---

This, actually, works. The key is to apply format before setting the value:

Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).Rows(2).Delete

wb.Sheets(1).Range("E3:F3").NumberFormat = "$#,##0.00"

For Row = 3 To 3 'your rows range in case you need iterate through (1 row only in your case)
    For Column = 5 To 6 'E3:F3
        wb.Sheets(1).Cells(Row, Column) = Mid(wb.Sheets(1).Cells(Row, Column), 2, Len(wb.Sheets(1).Cells(Row, Column)) - 2)
    Next Column
Next Row