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?
Microsoft Excel

Avatar of undefined
Last Comment
LockDev

8/22/2022 - Mon
Steve

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

ASKER
Steve,

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

Thanks,
Nick
Steve

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
LockDev

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steve

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.
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve

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)
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)