g Text Version
Beauty & Self
Books & Music
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
News & Politics
Religion & Spirituality
Travel & Culture
TV & Movies

Bored? Games!
Take a Quiz
Rate My Photo

Natural Living
Folklore and Mythology
Distance Learning

All times in EST

Full Schedule
g Desktop Publishing / Ebooks Site

BellaOnline's Desktop Publishing / Ebooks Editor


Excel - Convert Text to Numbers

Guest Author - Chris Curtis

Numbers are often stored as text when you bring data into Excel from an external program such as a main frame system. You are unable to manipulate and analyze the numeric data when it is stored as text. The good news is you can perform a simple technique to convert the data into numeric values. Here is how:

• Select an empty cell in your worksheet and type a 1.
• Copy the cell containing the 1
• Select the range of data that is stored as text
• Select Edit| Paste Special
• Select Multiply from the Operation section of the dialog box
• Click on OK

All of the data will now be numeric and you are able to analyze and perform mathematical calculations on it.

Negative values can give you problems when the negative sign is shown to the right of the number. Numeric notation in Excel always shows the minus sign to the left. Although a little more complex, you can deal with this in a similar manner. You use a nested formula using the Left and Len functions. The LEFT function returns the first character or characters in a text string, based on the number of characters you specify. The syntax is =LEFT(text,num_chars) where the text argument would reference the cell containing the text string and num_chars is the number of characters you want to return. You use the LEN function to determine the number of characters to return. The LEN function returns the length or number of characters in a text string. It takes on argument (text) which could be a reference to a cell containing the text. When you combine these two functions, you can truncate the right handed minus sign and then multiple the text times -1 value to arrive at the correct negative value. The syntax is:


Unfortunately, this technique is not quite as simple as the above conversion from text to numbers. To accomplish it you must use a blank column or row depending on the structure of your data. Establish your formula in a blank cell at the top of an available column. This column does not have to be adjacent to the column you want to convert. Reference the first cell you need to convert for the text argument in both the Left and Len functions. Copy the formula down the column until you reach the end of the data.

Your next step is to replace your actual data with your calculated numeric data. Select the data in the column you just created and copy it. Click in the first cell of the column containing the data needing to be converted. Right click and select Paste Special. Select values in the Paste Special dialog box. Delete the column with the calculated values. You will notice that their values changed because the values in your original column (which is referenced in your formula) changed.

Test the technique out on a small amount of data in a blank worksheet before you use it on a live data analysis.

Are you trying to build on your Excel Skills? Your Bella Desktop Publishing Host is teaching Excel Formulas and Functions – A Proficiency Series at Universal Class.

High Quality, Low Cost Online Courses

Be sure to also visit our sister site PC Advice at Bella. You will find a myriad of links and articles about Internet Issues, Personal Computing and Desktop Management.

Please contact your host with any questions. Please also feel free to make any suggestions or contribute a “Unique Technique” Suggestions should be accompanied by your name and title so that I may give the contributor credit.

Add Excel+%2D+Convert+Text+to+Numbers to Twitter Add Excel+%2D+Convert+Text+to+Numbers to Facebook Add Excel+%2D+Convert+Text+to+Numbers to MySpace Add Excel+%2D+Convert+Text+to+Numbers to Del.icio.us Digg Excel+%2D+Convert+Text+to+Numbers Add Excel+%2D+Convert+Text+to+Numbers to Yahoo My Web Add Excel+%2D+Convert+Text+to+Numbers to Google Bookmarks Add Excel+%2D+Convert+Text+to+Numbers to Stumbleupon Add Excel+%2D+Convert+Text+to+Numbers to Reddit

Build Wealth with Excel PMT Function
Excel - Arranging Multiple Worksheets
Using Named Ranges (Excel)
Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map

For FREE email updates, subscribe to the Desktop Publishing / Ebooks Newsletter

Past Issues

Printer Friendly
tell friend
Tell a Friend
Email Editor

Content copyright © 2015 by Chris Curtis. All rights reserved.
This content was written by Chris Curtis. If you wish to use this content in any manner, you need written permission. Contact Laura Nunn for details.


g features
Becoming a Fiction Editor

Making a Background Transparent Using Photoshop

Merging Letters in Word 2007

Archives | Site Map


Past Issues

Less than Monthly

BellaOnline on Facebook

| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2016 Minerva WebWorks LLC. All rights reserved.

BellaOnline Editor