The Problem (Q-score 6, ranked #197th of 303 in the Excel VBA archive)
The scenario as originally posted in 2015
I have imported a csv file from MySQL, documenting part numbers and descriptions. Some of these part numbers have values like 1234567890987654321, which is then shortened by excel to 1.23e18. Problem is, I cannot query a part with this formatted data.
Now I cannot feasibly go through every cell as there are just over 28000 of them. I have converted the row to text however this does not change the data in the cell.
The closest thing I have to a solution is deleting the cells and then undo-ing, which gets the number in a textual format but then gives me a ‘number in text field’ error.
Also some parts have part numbers like 12E345 which is then changed to 1200000000000000000000000000000000000000000000- you get the picture.
Very annoying…
I would like a batch process to change all the values to text format, thanks in advance.
Why community consensus is tight on this one
Across 303 Excel VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — niche answer (below median) (+8)
Advisory answer — community consensus with reference links
Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.
Instead of just opening the CSV in Excel, import it with Data -> External Sources -> From Text.
You will have to first pick basic things like “delimited” format, whether first row contains headers, separator etc.
In the third part of the “Text Import wizard”, you can pick data type of each column – picking Text for your columns will probably solve the problem.
When to Use It — classic (2013–2016)
Ranked #197th in its category — specialized fit
This pattern sits in the 98% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.
What changed between 2015 and 2026
The answer is 11 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.