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

Download excel example filetext qualifier.xls
(Excel 97-2003 Workbook *.xls)