We help IT Professionals succeed at work.

Excel Match function does not work because the data in the cell has a format error

LockDev
LockDev asked
on
5,602 Views
Last Modified: 2014-05-21
Hi,

Excel Match function does not work because the data in the cell has a format error. There is a little green mark on the upper left top of the cell. How do I remove the error from multiple cells so that the error goes away and the match function will work?
Comment
Watch Question

SteveCost Accountant
CERTIFIED EXPERT
Top Expert 2012

Commented:
If what you are matching has the green icon... preceed it with double negative... '--'

If what you are looking up to has the green icon preceed it with double colon+ampersand   ""&

This should solve the issue.

examples:
=MATCH(""&A1,B:B,0)
=MATCH(--A1,B:B,0)

you can remove the error by clicking the green flag and converting text to number.

for multiple cells:
 highlight the top cell,
then [ctrl]+[shift]+[down],
then scroll back to the top and use the green flag as per a single cell.

Author

Commented:
Steve,

Thank you for your help. Is it possible to highlight the whole column and remove the error?

Thanks,
Nick
SteveCost Accountant
CERTIFIED EXPERT
Top Expert 2012

Commented:
You can if the error is in the topmost cell.
But you likely have a text header line which stops that.

I would leave the data ass it is and use the double quote method.
It doesn't make much difference either.

Also, with excel 2010 having millions of rows, changing a whole column can give screwy results as excel tries to change a million plus empty cells.

Author

Commented:
I got the solution:

Select the entire range with green triangle.
Go to data tab - select Text to column option.
Press next 2 times than select general format & finish.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
SteveCost Accountant
CERTIFIED EXPERT
Top Expert 2012

Commented:
LockDev, please can you try to work with us to provide a solution for your needs...
Closing questions without working with the expert or acknowledging the assistance already provided is not going to find you the best solution and may alienate some experts who do not feel their efforts are appreciated (not just for you but others seeking help too).

Your original question has been answered (in my opinion) and there are plenty of ways to complete the task you wanted to acheive....
I would suggest the option below as it can be faster...
To perform this on the whole column:
Copy a whole empty column which is formatted as general (must have NO values in it)
then highlight the row to be corrected.
choose PasteSpecial  >  Operation > Add > OK
This will add the nothing in the empty cells to the other cells, thus converting text as numbers to simple numbers.

Author

Commented:
Steve,

I am sorry. I appreciate all your help.

The solution you just suggested will only paste the values that are numbers. However, the cells that have values that are text will not paste into the  new column. For example I have 2 cells "4510T" and "4640" (4640 shows the error) When I copy both values and PasteSpecial  >  Operation > Add > OK, I only get the number value.

Thank you again for all your time.

Nick
SteveCost Accountant
CERTIFIED EXPERT
Top Expert 2012

Commented:
Are you copying a Blank column onto your numbers to fix?
It sounds like you are copying your Values into a blank column.

You need to copy a blank column over your data column using the add function or you will lose those you want to keep as text.
(you may want to select the 'values' option with 'add' also)

Using text to column may work most times, but beware it splitting data across cells (as that is its purpose)

Author

Commented:
A blank field did not help.

At this point text to column is the best option (although I need to be careful about splitting data as you pointed out)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.