Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel numbers not working

Hi

I have a spread that was downloaded from an accounting system, where
the amount column contains numbers like the following. Excel isn't seeing these
as numbers. What Excel function or method can I use to turn these into numbers?

-,100.00
4 368.81
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
You could just use Replace to remove commas and spaces.
Avatar of yahooooo
yahooooo
Flag of United Kingdom of Great Britain and Northern Ireland image
update column, lastrow and spreadsheet to your needs
Dim i As Long
Dim MyStr As String
Dim MyNewStr As String
lastrow = Sheets(1).Range("A" & Sheets(1).Rows.count).End(xlUp).Row

For i = 2 To lastrow

    MyStr = Sheets(1).Range("A" & i).Value
    MyNewStr = WorksheetFunction.Substitute(MyStr, "-", "")
    MyNewStr = WorksheetFunction.Substitute(MyStr, ".", "")
    MyNewStr = WorksheetFunction.Substitute(MyStr, ",", "")
    MyNewStr = WorksheetFunction.Substitute(MyStr, " ", "")
    Sheets(1).Range("A" & i).Value = MyNewStr
Next

Open in new window

Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks yahooooo. I ran the code and for some reason it didn't work. Please have a look  at the attached spreadsheet, column R
Magic-Wand-GL-2010.xls
ASKER CERTIFIED SOLUTION
Avatar of yahooooo
yahooooo
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks very much for all the help