Solved

Excel Number Formatting

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In the hope of saving someone else's sanity... About a year ago we bought a Cisco 1921 router with two ADSL/VDSL EHWIC cards to load balance local network traffic over the two broadband lines we have, but we couldn't get the routing to work consi…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now