Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Cannot convert text to numbers

0

I have a worksheet with numbers on the left side of a cell which I presume is in text format.  None of the prescribed methods of conversion eg Paste Special - Multiply works; I get a VALUE! error message.  Using the Cell("type") function, I see the Label type of data in the cell.  How can I change the text to a number?  Thanks in advance.

Answer
Discuss

Answers

0
Selected Answer

Consult Excel help for the TEXT function.  Use something like =TEXT(A1,"$0.00") for Dollar type formatting OR whatever your formatting desire is.  If you still have issues, check the formatting of the cell you're in and changed to some type of Number format.

Discuss

Discussion

Many thanks for the prompt reply.
I tried using the TEXT function and on a cell with the number on the right hand side, I can change from, say 266 to 266.00 by using a different type. However, when the number is on the left hand side, it remains the same as 266 using the TEXT function
 I have compared the options for the CELL function between the righthand side and lefthand side numbers and the only difference is the "type" option.  The left hand side shows the cell contains a text constant - the right hand side shows "v" which Excel says it means "all others".  I don't understand this.
kenrock (rep: 2) Jul 28, '17 at 11:41 am
I'm not sure why you are using the CELL Function? 
But Check the FORMAT CELLS it should be on the Ribbon (I'm unfamiliar with Excel for Mac) and you should be able to choose: General, Number, Currency, Accounting etc...if your cell is listed as "TEXT" you will want to change it to a number format.
queue (rep: 467) Jul 28, '17 at 12:32 pm
Once again, thank you for your reply.
 I use the CELLS function because I wish to understand what is going on in the cell.  When I use the NUMBER option in "FORMAT CELLS" on one of these 'rogue" cells, the cell remains the same ie it remains on the lefthand side.  If I use another empty cell to see the result of dividing this formatted 'rogue' cell by, say, 2, I see"hash VALUE!".  It hasn't been re-formatted.
kenrock (rep: 2) Jul 28, '17 at 6:12 pm
Add to Discussion
1

The most likely source of your problem is the use of the non-breaking space character CHAR(160) , which is what databases like to do. If you have the time to treat your data it can be removed, but then you also might have thousands separators or currency symbols. Therefore I sugged a radical cure that deals with all of these and guarantees the return of a number, to wit, the following UDF.

Function GetValue(Cell As Range) As Double
    
    Const Permitted As String = "01234567890."
    
    Dim Fun As String
    Dim Txt As String
    Dim Ch As String
    Dim n As Long
    
    Txt = Trim(Cell.Value)
    For n = 1 To Len(Txt)
        Ch = Mid(Txt, n, 1)
        If InStr(Permitted, Ch) Then
            Fun = Fun & Ch
        End If
    Next n
    GetValue = Val(Fun)
End Function

It must be installed in a standard module. Click Alt+F11 to open the VB Editor. Click on the workbook in the project explorer on the left of the screen. Select Insert > Module and paste the code in the Code area which comes up. After this the workbook must be saved as macro-enabled (xlsm format).

In the worksheet, treat the function like any other. Enter =GetValue(A1) and the cell will display the value of A1. You can treat the result of GetValue like any other number. Multiply, divide, add or subtract, like =GetValue(A1) * 100.

Discuss

Discussion

Yes!  An extra character (Asc202) had been added to the string!  In future, I will know how to recognise the problem.
Many thanks to both Variatus and Queue for your interest and advice.
kenrock (rep: 2) Jul 29, '17 at 2:13 am
If this answer solved the problem, please select it so that future readers can more easily find the solution and Variatus can get recognition for it.
don (rep: 1989) Jul 29, '17 at 3:22 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login