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.


Reply
 
LinkBack Thread Tools Display Modes
Share |
  #1 (permalink)  
Old 08-02-2010, 03:15 PM
kwvarga's Avatar
v7n Mentor
Latest Blog:
None

 
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
  #2 (permalink)  
Old 08-09-2010, 06:16 PM
lynchburg computers's Avatar
Contributing Member
Latest Blog:
None

 
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

Reply


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
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


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 01: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 03: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 08:35 PM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2014 Escalate Media




Search Engine Optimization by vBSEO 3.6.0 RC 2 ©2011, Crawlability, Inc.