SOLVED

What is this invisible data format?

Microsoft

Hello, I have received a data file where columns C,D,E (see image 1) have numbers in them. The data format shows as "General". However, when I do a lookup (vlookup/xlookup), it can't find a single match on the C column for example. My formula was correct. Then I double-clicked on a cell of col C, and after Enter, it immediately right-aligned itself (see image 2, where you can see the first 2 cells are right-aligned after double-clicking on each, or simply Enter when it's selected in the formula bar)! The lookup functions work perfectly on those. So, what's hiding in those cells? And how can I a) Identify this problem 2) and quickly reformat them instead of manually double-clicking on each cell? (I've tried Clear Formatting, changing to numbers, accounting, used CLEAN() but nothing worked to fix it other manually double-clicking). I've seen this before, but any help on how to identify and resolve it efficiently is greatly appreciated. This is for a Microsoft project. Thank you.

image 1 (original): 1.png

image 2 (after double-clickli2.png

7 Replies

@tonyr1129 

This often happens when data are imported from another source (for example a web page) into an Excel worksheet. Although the data look like numbers, Excel sees them as text.

A simple way to convert them to 'real' numbers in one go:

  • Select an empty cell.
  • Copy it to the clipboard.
  • Select columns C, D and E.
  • Right-click anywhere in the selection.
  • Select Paste Special... from the context menu.
  • Select Add.
  • Click OK.

@tonyr1129 

Or don't care about formats and convert directly in formula adding double dash in front of lookup array reference.

@Sergei Baklan Thank you for your suggestion. So that I understand correctly, let's say my formula is: =VLOOKUP([@[Current PartnerONE ID]],FY20MPLoffboard!$A$2:$C$510,3,FALSE)

(and let's also assume FY20MPLoffboard is the sheet that contains this trouble-some columns)

Is your suggestion then to rewrite the formula as below?

=VLOOKUP([@[Current PartnerONE ID]],--FY20MPLoffboard!$A$2:$C$510,3,FALSE)


Can you please confirm? That'd be a real time-saver if double-dash can work through this type of issues!

 

@Hans Vogelaar Thank you for your suggestion! I thought about it, now I know that's actually used by others as a workaround too.

best response confirmed by tonyr1129 (Microsoft)
Solution

@tonyr1129 

Not exactly, that's like

=VLOOKUP([@[Current PartnerONE ID]],--FY20MPLoffboard!$A$2:$C$510,3,FALSE)

alternatively

=VLOOKUP([@[Current PartnerONE ID]],VALUE(FY20MPLoffboard!$A$2:$C$510),3,FALSE)
or
=VLOOKUP([@[Current PartnerONE ID]],1*FY20MPLoffboard!$A$2:$C$510,3,FALSE)
or like

@Sergei Baklan These are really great examples. Thank you! The first suggestion that I edited also seems to work. 

@tonyr1129 , you are welcome

1 best response

Accepted Solutions
best response confirmed by tonyr1129 (Microsoft)
Solution

@tonyr1129 

Not exactly, that's like

=VLOOKUP([@[Current PartnerONE ID]],--FY20MPLoffboard!$A$2:$C$510,3,FALSE)

alternatively

=VLOOKUP([@[Current PartnerONE ID]],VALUE(FY20MPLoffboard!$A$2:$C$510),3,FALSE)
or
=VLOOKUP([@[Current PartnerONE ID]],1*FY20MPLoffboard!$A$2:$C$510,3,FALSE)
or like

View solution in original post