Text to columns

[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"

 

[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.

 

[Nigel]

Hi Gary

Ok I've done that and the answer is 13

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.

 

[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

 

[Nigel]

Hi Gary

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

 

[Dave Peterson]

Time to find out what that other character is.

Use the same technique as before.

 

[Nigel]

Hi Dave

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