Link to home
Start Free TrialLog in
Avatar of LockDev
LockDev

asked on

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

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?
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of LockDev
LockDev

ASKER

Steve,

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

Thanks,
Nick
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.
Avatar of LockDev

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of LockDev
LockDev

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of LockDev

ASKER

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
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)
Avatar of LockDev

ASKER

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)