Excel vba: Use text qualifiers to make text to columns conversion easier
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 FunctionExample
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
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
- Select a cell (B1)
- Type =Ins_text_qualifiers(A1) into formula bar and press ENTER
Text to columns (Excel 2007)
- Select "Data" tab on the ribbon
- Click "Text to columns" button
Download excel example file
text qualifier.xls
(Excel 97-2003 Workbook *.xls)
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
December 13th, 2010 at 4:57 pm
@Oscar,
First off, I just wanted to let you know your posted code got messed up at the first For statement... that line became combined with the line that follows it. Second, you have these two lines of code in your code...
If Asc(Mid(Str, i, 1)) >= 65 And Asc(Mid(Str, i, 1)) < = 90 Or _
Asc(Mid(Str, i, 1)) >= 90 And Asc(Mid(Str, i, 1)) < = 122 Then
If Asc(Mid(Str, Len(Str), 1)) >= 65 And Asc(Mid(Str, _
Len(Str), 1)) < = 90 Or Asc(Mid(Str, Len(Str), 1)) >= 90 And _
Asc(Mid(Str, Len(Str), 1)) < = 122 Then Str = Str & "'"
Both of the tested ranges meet at ASCII 90 meaning they can be replaced by these single range tests instead...
If Asc(Mid(Str, i, 1)) >= 65 And Asc(Mid(Str, i, 1)) < = 122 Then
If Asc(Mid(Str, Len(Str), 1)) >= 65 And _
Asc(Mid(Str, Len(Str), 1)) <= 122 Then Str = Str & "'"
If that is really what you meant for your test range, then I wanted to point out your original two statements can be greatly simplified using the Like operator to these...
If Mid(Str, i, 1) Like "[A-z]" Then
If Mid(Str, Len(Str), 1) Like "[A-z]" Then Str = Str & "'"
Now, I'm guessing you didn't mean the two ranges being tested to meet as the letter "Z"; rather, I think you may have simply mistyped 90 in the second range for 97 (the ASCII code for "a"). If that is the case, you will need to change your posted code and the code in your example worksheet accordingly. Oh, if you want, the Like operator can help out for this as well...
If Asc(Mid(Str, i, 1)) Like "[A-Za-z]" Then
If Asc(Mid(Str, Len(Str), 1)) Like "[A-Za-z]" Then Str = Str & "'"
December 13th, 2010 at 8:59 pm
I have made the corrections!
Thanks!!