Handling ASCII #29 character in Excel

If you need to either replace one more text characters with ASCII #29 to prepare multi-value data for import to Portfolio, or want to do the reverse with Portfolio-exported text data, this should help.

The simplest method is to use an Excel formula and should work for all Mac/Win versions of Excel. For this exercise take a blank sheet and type - without quotes - "Ant, Bee, Cat, Dog" into cell A1. In cell B1, type without quotes the formula "=SUBSTITUTE(A1,", ",CHAR(29))". On screen in B1 you should see the original text but with an empty square replacing each comma+space sequence in the A1 value.

To recreate the text in Column A, type into C1 "=SUBSTITUTE(B1,CHAR(29),", ")". Well done you've reversed the original text transformation.

Clearly, you don't need to use column A or row 1 and the output needn't be in the cell to the right of the source. Just ensure the first argument of SUBSTITUTE is the cell location of the text to transform. Similarly, you don't need to type the formula in each cell, select the cell with the formula and click on the black square and drag downwards, invoking Excel's fill-by-example mode. In the above example cell B2 would show "=SUBSTITUTE(A2,", ",CHAR(29))", cell B3 "=SUBSTITUTE(A3,", ",CHAR(29))", etc.

As you can see, you can either insert or remove the ASCII #29 character. For large datasets, and for those already familiar with VBA** you can use macros instead. ** VBA - Visual Basic for Applications is built into both Windows and Mac versions of Excel.

Keywords (to assist indexing/searching):

Question: Handling ASCII #29 character in Excel [FAQ00387.htm]
Last Update:- 27 November 2006

This FAQ is created and maintained using

Friday - The Automatic FAQ Maker