## [Solved] VLOOKUP problem working with cell formatting

### [Solved] VLOOKUP problem working with cell formatting

Hello,

I cannot find any reason for the vLookup function to work on cells formatted as Text but with characters such as - or 1/2 etc.

Thanks
Attachments
Last edited by Hagar Delest on Sun May 02, 2021 11:08 pm, edited 1 time in total.
Reason: tagged solved.
Office ver : 7.1.2.2
OP: Windows 10
brubble51a

Posts: 3
Joined: Sun May 02, 2021 8:32 pm

### Re: Vlookup problem working with cell formatting - possibly

Hi, and welcome to the forum. Thank you for the attachment.

brubble51a wrote:cells formatted as Text
Cell formatting and cell values are independent in Calc. Cells formatted as numbers can contain text values. Cells formatted as text can contain numeric values. Your cells are not formatted as text. Format → Cells shows that the category is General. Some of the cells have numeric values, like B2 on the first sheet. Some contain text like B3, the cell below it. Use View → Value Highlighting to see the difference.
1. Types of data in Ten concepts that every Calc user should know

brubble51a wrote:I cannot find any reason for the vLookup function to …
I presume you are asking why VLOOKUP returns #N/A. This is because the lookup value is not found in your range \$Sheet2.\$A\$1:\$C\$1686. Let's check one: =EXACT('Price List'.B3;Sheet2.A2). Oops, that's FALSE though they look identical (01610-1/2 and 01610-1/2).
[Tutorial] VLOOKUP questions and answers, especially Q15, Q16. Q17

Check the lengths: =LEN('Price List'.B3)=LEN(Sheet2.A2). That's FALSE too. The problem is that you have unexpected characters in Sheet2. =DEC2HEX(UNICODE('Price List'.B3);4) is 0030 as expected but =DEC2HEX(UNICODE(Sheet2.A2);4) is 200E. Cells A2:A21 begin with invisible character U+200E (Left-to-Right Mark), not U+0030 (zero). Use Edit → Find&Replace → More options, select Regular Expressions, Search for \u200E, Replace with «nothing», Replace All. I recommend unchecking Regular expressions before clicking Close.

You should set all of the part number cells to use text formatting and then convert any numeric values in them to text. First set Format → Cells → Category → Text (or preferably use a style). You accomplish numeric to text conversion with [Tutorial] Text to Columns. In the Text Import dialog, set the column type to Text, not Standard. These part numbers are codes and should be stored as text. You won't be doing any arithmetic with part numbers, like taking square roots. Always store codes as text.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 4006
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### SOLVED

Much Thanks Mr. Programmer !!!
That solved the problem I was having.
Office ver : 7.1.2.2
OP: Windows 10
brubble51a

Posts: 3
Joined: Sun May 02, 2021 8:32 pm

### Re: [Solved] VLOOKUP problem working with cell formatting

Can Mr. Programmer contact me through email?
Office ver : 7.1.2.2
OP: Windows 10
brubble51a

Posts: 3
Joined: Sun May 02, 2021 8:32 pm

### Who is online

Users browsing this forum: No registered users and 16 guests