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

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

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