Text to columns

N

Nigel

I have exported a table from MS Access to Excel to be able to use it as a
mailing list. When I try to convert text to columns I am unable to enter the
text delimiter as it is some sort of white square, well in fact there are two
squares together. Can any one help?
I've pasted a sample here but it shows without the squares and the text has
wrapped.

"Beech House
Witham Park
Waterside South"
 
G

Gary''s Student

Some detective work is required:

1. copy a typical item into an unused cell, say Z100
2. using the fomula bar, remove all characters except for a single small
square
3. In Z101 enter:
=CODE(Z1)

Once we know the code, we can change it into something Text to Columns can
use.
 
N

Nigel

Hi Gary

Ok I've done that and the answer is 13

Gary''s Student said:
Some detective work is required:

1. copy a typical item into an unused cell, say Z100
2. using the fomula bar, remove all characters except for a single small
square
3. In Z101 enter:
=CODE(Z1)

Once we know the code, we can change it into something Text to Columns can
use.
 
G

Gary''s Student

Now enter and run this small macro:

Sub boxkiller()
For Each r In ActiveSheet.UsedRange
r.Value = Replace(r.Value, Chr(13), "^")
Next
End Sub



It will change all the "boxes" into ^
We can then use Text to Columns with the ^ as a separator:


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
N

Nigel

Hi Gary

The macro worked but only removed/changed one of the pair of boxes. Running
the code again does nothing. Sorry!
 
D

Dave Peterson

Time to find out what that other character is.

Use the same technique as before.
 
N

Nigel

Hi Dave

I've just done that, character was "10", altered the macro and hey presto.
Thanks Dave and Gary, brilliant!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top