This blog post describes how to create text qualifers and make "text to columns" conversion easier.

Example

I copied a table from Wells Fargo annual report (pdf).

Pasted it into an excel sheet.

Converted text to columns.

The table is a mess. Text and numbers are converted into a single cell each.

But in "text to columns" wizard you can select a text qualifer to convert words into a single cell.

This udf inserts ' (apostrophe) to put words into one cell:

```Function Ins_text_qualifiers(Str)
Dim TLen As Long
Dim i As Long
Dim j As Long
TLen = Len(Str)Str = Trim(Str)
For i = 1 To TLen
If j = TLen + 1 Then Exit For
If Asc(Mid(Str, i, 1)) >= 65 And Asc(Mid(Str, i, 1)) <= 90 _
Or Asc(Mid(Str, i, 1)) >= 97 And Asc(Mid(Str, i, 1)) <= 122 Then
Str = Left(Str, i - 1) & "'" & Mid(Str, i, TLen)
i = i + 1
For j = i To TLen
If Asc(Mid(Str, j, 1)) >= 48 And Asc(Mid(Str, j, 1)) <= 57 Then
Str = Left(Str, j - 2) & "'" & Mid(Str, j - 1, TLen)
i = j
Exit For
End If
Next j  End IfNext i
If Asc(Mid(Str, Len(Str), 1)) >= 65 And Asc(Mid(Str, Len(Str), 1)) <= 90 _
Or Asc(Mid(Str, Len(Str), 1)) >= 97 And Asc(Mid(Str, Len(Str), 1)) <= 122 Then Str = Str & "'"
Ins_text_qualifiers = Str
End Function```

### Example

per share amounts) 2009 2008 2007 2006 2005 2004 2008 growth rate

becomes

'per share amounts) ' 2009 2008 2007 2006 2005 2004 2008 'growth rate'

### The final result

It is not perfect but it is a lot better.

### How to implement user defined function in excel

1. Press Alt-F11 to open visual basic editor
2. Click Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor
5. Select a cell (B1)
6. Type =Ins_text_qualifiers(A1) into formula bar and press ENTER

### Text to columns (Excel 2007)

1. Select "Data" tab on the ribbon
2. Click "Text to columns" button