Robert has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Converting Text to Numbers
I've opened CSV data in my spreadsheet that includes text and numbers (the numbers are in currency form e.g £25).
However, Excel does not recognise the numbers as numbers- thus I cannot add/subtract them or use any functions or formula.
I've tried reformating them as numbers (using format cells), used the text to column command and multipled them by 1 using the paste special command...however none of this has worked.
Is there a way of telling Excel when I open the CSV data that certain columns are 'number'?
Alternatively, are there any macro's that would read the 'text' data in a cell and replace it with 'numbers'?
RE: Converting Text to Numbers
Hi Robert
Thanks for getting in touch. It's an interesting problem, when it looks like a number but doesn't behave like a number!
Use the VALUE function: this converts numbers masquerading as text into proper numbers. You use it like this:
=VALUE(A1)
The currency symbols might cause some issues; let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
