How to make sure all excel cells formatted as text in a column actually are

Question

I have a column of content submitted by multiple users, generally pasted into a sheet, from multiple sources. This column has numbers that should always be formatted as text. In fact, no matter how this is done, there are always a few items that never have the indicator in the left corner warning that these are formatted as text (which we want to see in all cases)

Checking the individual cell, it does show as formatted text, but in reality on an import into a datatable, if the indicator is missing, the datatype is imported as a number. Clicking after the number and hitting Enter will change the indicator to text.

How can I do that in VBA? I don't want to visit each cell, click on the end of the content and hit enter.Cutting and paste special in no combination reliably fixes these.

What does excel look at, which gets the format issue right with these text format warning indicators, and yet doesn't seem to get it right when you look at the cell format properties?

Excel 2003 but have had the same issue in later versions too.

Solution

What worked for me was to check the error indicator, which seemed more reliable than the cell format itself. This looks for anything missing the indicator and forces it to be text.

Unless someone knows something further concerning why this should NOT be done, it solves my issue.

Sub check4textformat()
 For Each cell In Range("E2:E15000")
 If cell.Errors.Item(xlNumberAsText).Value = False Then cell.Formula = cell.Text
 Next
End Sub

Comments

[datatoo]

if I prepend the "'" to each of these, what might occur on an import to a table. I don't want to import "'" into a fixed length field and risk truncating the content

[Jesse]

You won't unless you import formulas. If you import values you won't include the '.

[datatoo]

actually the excel file is a linked table used in queries. I will have to test how the "'" pre-pending is treated thank you

[Zac]

This worked for me! Although Value needed to be set to TRUE for it to work.