logo
g Text Version
Beauty & Self
Books & Music
Career
Computers
Education
Family
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
Money
News & Politics
Relationships
Religion & Spirituality
Sports
Travel & Culture
TV & Movies

dailyclick
Bored? Games!
Nutrition
Postcards
Take a Quiz
Rate My Photo

new
European Travel
Action Movies
Bible Basics
Houseplants
Romance Movies
Creativity
Family Travel


dailyclick
All times in EST

Clairvoyance: 08:00 PM

Full Schedule
g
g Desktop Publishing / Ebooks Site

BellaOnline's Desktop Publishing / Ebooks Editor

g

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:

=LEFT(A2,LEN(A2)-1*-1)

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)
RSS
Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map


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


Past Issues


print
Printer Friendly
bookmark
Bookmark
tell friend
Tell a Friend
forum
Forum
email
Email Editor


Content copyright © 2014 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


g features
Becoming a Fiction Editor

Making a Background Transparent Using Photoshop

Merging Letters in Word 2007

Archives | Site Map

forum
Forum
email
Contact

Past Issues
memberscenter


vote
Poetry
Daily
Weekly
Monthly
Less than Monthly



BellaOnline on Facebook
g


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


BellaOnline Editor