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

Example

I copied a table from Wells Fargo annual report (pdf), see image above. Paste it into an excel sheet.

I then used "Text to Columns":

1. Select column A
2. Go to tab "Data"
3. Press with left mouse button on "Text to Columns" button.

I got the following result, see image below. Each word is split into a column each, this is not what is wanted.

The table is a mess, however, the "text to columns" wizard allows you to select a text qualifer to convert words into a single cell.

This custom function inserts ' (apostrophe) before and after text. The rules are if a character is a number and the next character is a letter then insert an apostrophe before the letter.

The same thing if a character is a letter and the next character is number then insert a apostrophe before the number.

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 add the user-defined function to your workbook

(The macro shown in the image above is not used in this article, it only shows you where to paste the code.)

1. Press Alt-F11 to open the visual basic editor
2. Press with left mouse button on 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. Press with left mouse button on "Text to columns" button

Get the Excel file

text-qualifier1.xls