How Can I Convert Numbers, Which are Formatted as Text, to Actual Numbers in Excel

Hi:

someone gave me some data to analyze in an Excel spreadsheet. The numbers (dollars actually) are not numeric, but text. (I have attached a sample)

How can I convert these to numbers that can be analyzed?

Thanks

Rex
EE-Sales-Example.xlsx
RexQuality LeaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConnerTCommented:

1.

Select one column of cells that contain the text.

2.

On the Data menu, click Text to Columns.

3.

Under Original data type, click Delimited, and click Next.

4.

Under Delimiters, click to select the Tab check box, and click Next.

5.

Under Column data format, click General.

6.

Click Advanced and make any appropriate settings for the Decimal separator and Thousands separator. Click OK.

7.

Click Finish.
The text is converted to numbers.

Hope This Helps !
zorvek (Kevin Jones)ConsultantCommented:
To convert text values to numeric values, enter zero in an unused cell, select the cell and press CTRL+C, select the cells to convert, select Paste Special (from the Paste drop down in the Clipboard group of the Home tab,) select the Add option, and click OK.

Kevin
RexQuality LeaderAuthor Commented:
I'm sorry, but neither of those techniques worked for me.
zorvek (Kevin Jones)ConsultantCommented:
You need to get rid of the non-breaking space. To find and replace instances of the non-breaking space character, select the cells to be cleaned and press CTRL+H to open the Replace dialog. Click in the "Find what" text entry field and clear any text (press CTRL+LEFT, press CTRL+SHIFT+RIGHT, and press DELETE).  While holding the ALT key down enter 0160 on the numeric keypad. Click in the "Replace with" text entry field and clear any text (press CTRL+LEFT, press CTRL+SHIFT+RIGHT, and press DELETE). Click the "Replace All" command button.

Note: If using a laptop put the keyboard into number lock mode (by pressing the "Num Lk" key) and enter the numbers using the numeric keypad by pressing FN+ALT+digit where digit is any of the keys M=0, J=1, K=2, L=3, U=4, I=5, O=6, 7=7, 8=8, or 9=9. Do not use the regular number keys at the top of the keyboard. To turn off number lock press the "Num Lk" key again. Number lock mode is usually indicated with a lit "9" somewhere in the control light area.

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RexQuality LeaderAuthor Commented:
Thanks. (That was the most screwed up data I've ever seen)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.