Excel number from database dump not treated as number

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

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?
Comment
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

Regards,
Brian.
Most Valuable Expert 2011
Top Expert 2011
Commented:
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

Author

Commented:
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