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

[Alinator]

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:

=VLOOKUP(A1;$b.A:A;1;0)

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.

[JohnSUN]

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

[Alinator]

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?

[JohnSUN]

Try this

=VLOOKUP(TEXT(A1;"@");$b.$A:$A;1;0)