Excel number from database dump not treated as number

Murray Brown
Murray Brown used Ask the Experts™

I have an issue with a number that is part of a database dump from
Pastel evolution. It has a space after the first number
1 809.38
and Excel doesn't see this as a number. I have a hundred thousand records.
How can I convert this to a number using an Excel formula?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi, murnro.

Assuming your data is in Column A, please enter the following in B1 and copy down...
=SUBSTITUTE(A1," ","")*1

Most Valuable Expert 2011
Top Expert 2011
or just select it, ctrl+H, enter a space in the Find box, leave the Replace box blank and press replace all
Murray BrownASP.net/VBA/VSTO Developer


Excellent answers. Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial