Green triangle headache.

[Lythande]

Hey board,

I have a bunch of seven-digit numbers in column G in a report I download from a vendor. My goal is to see what matches there are in another list of such numbers. I use a formula* to give me a Match or #N/A, in the column H, which has worked fine in other sheets.

The problem in this sheet is that matches show up as #N/A. If I manually type in a number from the list on the sheet it "matches" and tells me what row the number is on in my list on the other sheet.

The difference is that in my downloaded spreadsheet all numbers in row G have a green triangle in the upper left corner, and when I click it and select "convert to number" the #N/A changes to a match as it should, so it's something messed up in the formatting of the numbers in column G that are messing things up. However, I don't want to manually do this for each one, but I can't seem to do this for the whole column. I tried by hilighting comumn G, right clicking on Format Cells, then selecting the option of Number, but it didn't work.

This is most annoying.

Any thoughts or advice would be greatly appreciated.

 

[VoG]

Try selecting them, Data > Text to Columns, make sure Delimited is selected and click Finish.

 

[Rekd]

Also, if the numbers begin with zero Excel may trim them.

If you have problems converting, enter a "1" into a cell, copy it, select all the cells with the vendor numbers, right click/paste special/multiply then hit ENTER.

Another thing to try is select the cell, hit F2 then ENTER.

Hope this helps.

 

[Lythande]

Thanks guys!