## 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 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

- 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)

### Category: Vba

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Comments(40) Filed in category: Excel, VBA

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]

Comments(30) Filed in category: Check-boxes, Excel, VBA

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

Comments(24) Filed in category: Excel, VBA

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. […]

Comments(17) Filed in category: Drop down lists, Excel, VBA

Let me show you how to create self adjusting columns in excel with a few lines of vba code. Example, […]

Comments(16) Filed in category: Excel, VBA

Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter […]

Comments(16) Filed in category: Excel, VBA

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Comments(14) Filed in category: Advanced filter, Excel, Unique distinct values, VBA

### 2 Responses to “Excel vba: Use text qualifiers to make text to columns conversion easier”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

@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!!