Solved

Excel Number Formatting

Posted on 2014-12-19
3
96 Views
Last Modified: 2014-12-19
Environment: Excel 2013 on Win 7 Pro x64, both fully patched

I have a spreadsheet column with about 5500 random numbers in it. I have a VB macro to compare these numbers to another column to detect duplicates. The column i am using for comparison was imported from a CSV file, and had spaces in it, which i used TRIM to fix. Now the entire column has the little green triangle indicating an error stating "The number is formatted as text or has a preceding apostrophe". I have selected the entire range of cells, and changed the format to Number on the home tab, but it only fixes the top most cell. I have also tried selecting the entire range, right clicking, and setting the cell format that way, but it has no effect. The green triangle remains. So far the only way I have gotten rid of it is to double click in each cell and then click elsewhere, which immediately correctly formats the cell, but I am not going to do that 5500 times. Please advise on how to correct the issue.
0
Comment
Question by:BBstaff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:BBstaff
ID: 40509793
Need to add, I also used SUBSTITUTE to rid the numbers of trailing letters and that seems to be the root cause of the issue at this point. No matter how I copy them over after I use that function, they copy over as TEXT. Im looking for a way to turn this list into pure numbers. So for clarity:
I took a column similar to this:            12345 DD    (five leading spaces)
I used TRIM to get                         12345 DD             ( leading spaces deleted)
I used SUBSTITUTE to get            12345                   (no trailing characters or spaces but i cannot paste the numbers into the comparison sheet with out the text formatting or the actual equation following)
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40509867
redo the substitute(trim()) process but use value(substitute(trim())) to get the final result as a numeric.

You can probably do it as a single operation.
=value(substitute(trim(cellreference)," ",""))

When you've got the block of data as numbers, do a select all then copy and paste special (values) into the comparison sheet.
0
 

Author Closing Comment

by:BBstaff
ID: 40509905
Simon, that did the trick! Thanks a million.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question