Excel to Database: How to deal with mixed data types columns of Excel sheet

Sometimes you need to import an Excel sheet into your database (such as Access) but one or more columns in the sheet contain mixed data types, both Text and Numeric. The import process may fail because of type mismatch.
You want or need to convert all values of those columns to Text values prior to import.

With small data, you can simply manually add ' in front of numeric data to make it become text but that is not a good way for large data

This is a problem I had to deal with daily when receiving data from someone else.

As seen, format as Text is not good enough and sometimes causes misleading as we cannot say a cell that displays a numeric text with left-alignment contains whether a Text value or a Numeric value.

With horizontal alignment setting as General and with NumberFormat setting also as General:
if a cell is left-alignment then that is Text,
if a cell is right-alignment then that is Number.

Setting NumberFormat as Text only works if we do it prior entering or editting data in Excel.
With existing numeric data, setting NumberFormat as Text only causes the data display with left-alignment but CANNOT convert Numeric values to Text values.

There is a little trick code that I developed to do it quickly for large data, an example as below:

I don't want to go through all cells in a column from top to bottom that is waste of time, so, in Sheet1:

1. Enter something in cell [A1] (to make sure Sheet1.UsedRange starts from [A1])

2. Make sure Number Format of column [C] is General (default) and its Horizontal Alignment is also General (default).

3. Enter some Texts and some Numbers in column [C].

4. Now you see that Text values are left-alignment and Numeric values are right-alignment.

5. Run the code below.

Code:

---

Sub ConvNumToText()
  Dim Cell As Range

  With Sheet1.UsedRange.Columns(3) '-- can use any range of your data
      .NumberFormat = "@"
      For Each Cell In .Cells
        If IsNumeric(Cell.Value) Then Cell.Value = CStr(Cell.Value)
      Next
      .NumberFormat = "General"
  End With
End Sub

---

6. Now you see that even with NumberFormat as General and Horizontal Alignment as General, all cells are left-alignment because all their values were converted to "real" Text.

7. However, if now you edit a "numeric text" cell, its value will become numeric again. To prevent this happens, comment out the line .NumberFormat = "General" above.