Ask Your Question

How do I format all number cells in a column as text?

asked 2016-03-17 11:25:02 +0100

Alinator gravatar image

I have two Calc sheets a and b. Both sheets have in column A a list of order numbers. I want to find out which elements of list a don't exist in list b and vice versa. I used a VLOOKUP in column B right next to the order number, for example cell B1:


The problem comes with different cell formats. Most order numbers are interpreted by Calc as text because they not only contain digits but also hyphens. But some order numbers are really numbers, i.e. they contain only digits. Those are interpreted by Calc as numbers (right indented). VLOOKUP seems to stumble upon this because it reports the digit-only order number as missing in the other sheet, even though it is there. I found this out when I got suspicious when all digit-only order numbers were reported missing. I looked manually and the reported-missing digit-only orders were not missing in the other list.

How can I get VLOOKUP to treat the digit-only order numbers as strings? I wondered about prefixing order numbers with ' but that would need to be done manually and the list is long.

edit retag flag offensive close merge delete


Do you mean something like as =VLOOKUP(TEXT(A1;"@");$b.$A:$A;1;0)? VLOOKUP works well for me without this trick

JohnSUN gravatar imageJohnSUN ( 2016-03-17 11:51:40 +0100 )edit

Yes, that's just what I needed :) Now digit-only orders are found in the other list. Do you want to formulate this as an answer so I can approve it?

Alinator gravatar imageAlinator ( 2016-03-17 12:29:18 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-03-17 12:33:37 +0100

JohnSUN gravatar image

Try this

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-03-17 11:25:02 +0100

Seen: 84 times

Last updated: Mar 17 '16