Unable to beat the text to number issue

Excel_Ghost

New Member
Joined
Mar 14, 2016
Messages
2
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
 

Attachments

  • Numbers Left aligned issue.png
    Numbers Left aligned issue.png
    144 KB · Views: 34
  • Numbers Left aligned issue (formulas).png
    Numbers Left aligned issue (formulas).png
    139.8 KB · Views: 35
  • Asap Uitlities.PNG
    Asap Uitlities.PNG
    104.8 KB · Views: 29
  • Asap Utilities Text to Number.PNG
    Asap Utilities Text to Number.PNG
    148.8 KB · Views: 34

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
What is the cell format for D27 and what happens if you f2 and then Enter on that cell?
 
Upvote 0
Hi Rory,
The format is Number and nothing changes by pressing F2 and then enter
 

Attachments

  • Cell Format D27.PNG
    Cell Format D27.PNG
    32.1 KB · Views: 15
Upvote 0
What does =LEN(D27) return?
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top