Solved

Excel Number Formatting

Posted on 2014-12-19
3
93 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
Viewers will learn how to find and create templates in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

751 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