Webmaster Forum

Go Back   Webmaster Forum > The Webmaster Forums > Tech Talk

Tech Talk Discuss computer issues, tech gadgets and hardware, operating systems, browsers, broadband and wireless, virus, trojan, and spyware help.

Thread Tools Display Modes
Share |
Old 08-02-2010, 03:15 PM
kwvarga's Avatar
kwvarga kwvarga is offline
v7n Mentor
Join Date: 10-13-03
Location: Tuscaloosa, AL or Atlanta
Posts: 2,522
iTrader: 0 / 0%
Solution for Excel Number Stored as Text

Hey all,

Sorry I don't post as much as I should over hear. Just wanted to post this here for anyone that may have run into the issue with Numbers Stored as Text in Excel.

My original blog post is here: Number Stored as Text Solution

Excel's 'Number Stored as Text' issue is tough to deal with, especially if you are having 3rd-parties fill out Excel templates for you. A lot of times when people copy and paste numbers into Excel, they are stored as 'Text' not 'Number's. This is an issue for any aggregation, summation, etc. functions that you may want to run on the data set. There are ways to fix them by hand, teach users to Paste Special -> Values, as well as correct the issue with VBA/Macros.

However, the solution I found after exhausting my googling abilities is much easier then all of those. All it takes is a little learning about Array Formulas, also know as Ctrl-Shift-Enter (CSE) Formulas. These array formulas allow a single function, such as VALUE() to take place on a large range. For example, doing =SUM(VALUE(A1:A100)) then pressing Ctrl-Shift-Enter (instead of just enter) will calculate the value of the cells, regardless if a Number is stored as a text or a number.

More information and screenshots can be seen on my blog post, but I wanted to share this useful tip with V7N
Reply With Quote



Old 08-09-2010, 06:16 PM
lynchburg computers's Avatar
lynchburg computers lynchburg computers is offline
Contributing Member
Join Date: 07-19-10
Location: Lynchburg
Posts: 49
iTrader: 0 / 0%
i always thought you could just paste and then format the cells as integers...that's what I have always done at least. Did that not work for you?
Reply With Quote
Go Back   Webmaster Forum > The Webmaster Forums > Tech Talk


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
number of html form text input boxes that user defines rickcook Coding Forum 17 02-13-2010 02:44 PM
Need a program that sorts a text list by number of characters in ascending order.... cobracommander Coding Forum 9 05-24-2007 11:04 PM
Blog Home Page - Number of Posts? - Full Text or Excerpts? Aviva Blogging Forum 9 12-14-2006 04:59 PM
Relationship between number of links and text Johan007 SEO Forum 3 05-05-2006 01:54 AM
Recommended number of anchor text RonHollingsworth SEO Forum 4 04-17-2004 06:30 PM

V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory

All times are GMT -7. The time now is 03:25 PM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC