Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
Rex85
Asked:
Rex85
  • 2
  • 2
1 Solution
 
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 !
0
 
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
0
 
Rex85Author Commented:
I'm sorry, but neither of those techniques worked for me.
0
 
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
0
 
Rex85Author Commented:
Thanks. (That was the most screwed up data I've ever seen)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now