Solved

Excel Number Formatting

Posted on 2014-12-19
3
80 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:
SimonAdept 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hyena v12.2 is now available for downloading and is available in English, French, German and Spanish versions.
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

743 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

10 Experts available now in Live!

Get 1:1 Help Now