Use text qualifiers to make text to columns conversion easier [VBA]
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":
- Select column A
- Go to tab "Data"
- 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.)
- Press Alt-F11 to open the visual basic editor
- Press with left mouse button on 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
- Press with left mouse button on "Text to columns" button
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
2 Responses to “Use text qualifiers to make text to columns conversion easier [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
@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 & "'"
I have made the corrections!
Thanks!!