Unable to beat the text to number issue

[Excel_Ghost]

Hi everyone.

I built a spreadsheet for my work to make the process more "automatic". I update it from time to time to improve functionality but this issue is now driving me nuts, and it wasn't here before so somewhere I did something I should not have. I use Asap Utilities to clean up texts before imports into Dynamics 365, to ensure there are no hidden chars, etc. copied over which can be picked up and error me.

All the numbers are stuck as left aligned (non-number). So I think it's best that I explain what's happening first (some will just pick up what I'm doing straight away), and then tell what I've tried so far.
This workbook I built is a catalog builder. It builds products and assigns the correct groupings/classes/financial dimensions/etc. according to my preset conditions. The sheet called "HS Codes - blue tab) pulls the HS Code (Harmonized System of tariff nomenclature, which is an internationally standardized system of names and numbers to classify traded products) that has been assigned to a specific product on the product building sheet (Cataloguer Product Application). Blue/Red tabs are import sheets (final data). Grey sheets are static meta data (matrix). Mustard tabs are working sheets (compiling/building of data).
For the description column I use the assigned HS code in the HS Code column to VLOOKUP the linked description from the list of HS codes on the HS Code Full list sheet (grey tab). You'll notice that each product is repeated 4x, once for every export country. If you look at A27:A30(Item M000177997), A31:A34(Item M000177998), A35:A38(Item M000177999), A43:A46(Item M000178001) and A51:A53(Item M000178003) you'll see it is all the same type of product (HS code is the same). Yet, some are left and some are right aligned.
It can't be centered or right aligned, indented, etc. I have looked at Cell Format, Styles, Conditional Formatting, AutoFormat and nothing looks out of the ordinary. This problem holds true to some numbers, but not to others of exact match. If I copy the 'working' and 'non-working' cells and paste as values, the 'non-working' cells show as text. I've also used Asap Utilities to Convert non-Number (text?) to numbers, in all the related cells, starting from the original data sheet and working through the functions paths until I get to the final Import file. So nothing works.
I know this problem seems trivial but it is going to keep me from getting work done until it is solved. Any ideas for a fix will be greatly appreciated.

Thanks

 

[RoryA]

What is the cell format for D27 and what happens if you f2 and then Enter on that cell?

 

[Excel_Ghost]

Hi Rory,
The format is Number and nothing changes by pressing F2 and then enter

 

[RoryA]

What does =LEN(D27) return?