SOLVED

What is this invisible data format?

%3CLINGO-SUB%20id%3D%22lingo-sub-1571701%22%20slang%3D%22en-US%22%3EWhat%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571701%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20received%20a%20data%20file%20where%20columns%20C%2CD%2CE%20(see%20image%201)%20have%20numbers%20in%20them.%20The%20data%20format%20shows%20as%20%22General%22.%20However%2C%20when%20I%20do%20a%20lookup%20(vlookup%2Fxlookup)%2C%20it%20can't%20find%20a%20single%20match%20on%20the%20C%20column%20for%20example.%20My%20formula%20was%20correct.%20Then%20I%20double-clicked%20on%20a%20cell%20of%20col%20C%2C%20and%20after%20Enter%2C%20it%20immediately%20right-aligned%20itself%20(see%20image%202%2C%20where%20you%20can%20see%20the%20first%202%20cells%20are%20right-aligned%20after%20double-clicking%20on%20each%2C%20or%20simply%20Enter%20when%20it's%20selected%20in%20the%20formula%20bar)!%20The%20lookup%20functions%20work%20perfectly%20on%20those.%20So%2C%20what's%20hiding%20in%20those%20cells%3F%20And%20how%20can%20I%20a)%20Identify%20this%20problem%202)%20and%20quickly%20reformat%20them%20instead%20of%20manually%20double-clicking%20on%20each%20cell%3F%20(I've%20tried%20Clear%20Formatting%2C%20changing%20to%20numbers%2C%20accounting%2C%20used%20CLEAN()%20but%20nothing%20worked%20to%20fix%20it%20other%20manually%20double-clicking).%20I've%20seen%20this%20before%2C%20but%20any%20help%20on%20how%20to%20identify%20and%20resolve%20it%20efficiently%20is%20greatly%20appreciated.%20This%20is%20for%20a%20Microsoft%20project.%20Thank%20you.%3C%2FP%3E%0A%3CP%3Eimage%201%20(original)%3A%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20269px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210819i16E78CB59D0B4F90%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eimage%202%20(after%20double-clickli%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%20275px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210820iC7476713CF85B47F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1571701%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571783%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571783%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751899%22%20target%3D%22_blank%22%3E%40tonyr1129%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20often%20happens%20when%20data%20are%20imported%20from%20another%20source%20(for%20example%20a%20web%20page)%20into%20an%20Excel%20worksheet.%20Although%20the%20data%20look%20like%20numbers%2C%20Excel%20sees%20them%20as%20text.%3C%2FP%3E%0A%3CP%3EA%20simple%20way%20to%20convert%20them%20to%20'real'%20numbers%20in%20one%20go%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20an%20empty%20cell.%3C%2FLI%3E%0A%3CLI%3ECopy%20it%20to%20the%20clipboard.%3C%2FLI%3E%0A%3CLI%3ESelect%20columns%20C%2C%20D%20and%20E.%3C%2FLI%3E%0A%3CLI%3ERight-click%20anywhere%20in%20the%20selection.%3C%2FLI%3E%0A%3CLI%3ESelect%20Paste%20Special...%20from%20the%20context%20menu.%3C%2FLI%3E%0A%3CLI%3ESelect%20Add.%3C%2FLI%3E%0A%3CLI%3EClick%20OK.%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571852%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751899%22%20target%3D%22_blank%22%3E%40tonyr1129%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20don't%20care%20about%20formats%20and%20convert%20directly%20in%20formula%20adding%20double%20dash%20in%20front%20of%20lookup%20array%20reference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571878%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20suggestion!%20I%20thought%20about%20it%2C%20now%20I%20know%20that's%20actually%20used%20by%20others%20as%20a%20workaround%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571922%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751899%22%20target%3D%22_blank%22%3E%40tonyr1129%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20exactly%2C%20that's%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(%5B%40%5BCurrent%20PartnerONE%20ID%5D%5D%2C--FY20MPLoffboard!%24A%242%3A%24C%24510%2C3%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ealternatively%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(%5B%40%5BCurrent%20PartnerONE%20ID%5D%5D%2CVALUE(FY20MPLoffboard!%24A%242%3A%24C%24510)%2C3%2CFALSE)%0Aor%0A%3DVLOOKUP(%5B%40%5BCurrent%20PartnerONE%20ID%5D%5D%2C1*FY20MPLoffboard!%24A%242%3A%24C%24510%2C3%2CFALSE)%0Aor%20like%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571874%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20suggestion.%20So%20that%20I%20understand%20correctly%2C%20let's%20say%20my%20formula%20is%3A%26nbsp%3B%3DVLOOKUP(%5B%40%5BCurrent%20PartnerONE%20ID%5D%5D%2CFY20MPLoffboard!%24A%242%3A%24C%24510%2C3%2CFALSE)%3C%2FP%3E%0A%3CP%3E(and%20let's%20also%20assume%20FY20MPLoffboard%20is%20the%20sheet%20that%20contains%20this%20trouble-some%20columns)%3C%2FP%3E%0A%3CP%3EIs%20your%20suggestion%20then%20to%20rewrite%20the%20formula%20as%20below%3F%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(%5B%40%5BCurrent%20PartnerONE%20ID%5D%5D%2C--FY20MPLoffboard!%24A%242%3A%24C%24510%2C3%2CFALSE)%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3ECan%20you%20please%20confirm%3F%20That'd%20be%20a%20real%20time-saver%20if%20double-dash%20can%20work%20through%20this%20type%20of%20issues!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572030%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThese%20are%20really%20great%20examples.%20Thank%20you!%20The%20first%20suggestion%20that%20I%20edited%20also%20seems%20to%20work.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572814%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20this%20invisible%20data%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572814%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751899%22%20target%3D%22_blank%22%3E%40tonyr1129%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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.
Highlighted

@tonyr1129 

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

Highlighted

@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!

 

Highlighted

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

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

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

Highlighted

@tonyr1129 , you are welcome