Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • Last Modified:

Excel Number Formatting

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
BBstaff
Asked:
BBstaff
  • 2
1 Solution
 
BBstaffAuthor Commented:
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
 
SimonCommented:
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
 
BBstaffAuthor Commented:
Simon, that did the trick! Thanks a million.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now