DB4 imported data

[Sasha]

Hi, been given an excel spread sheet that has been imported from a DB4
address database.

house number street name town and zip / postcode are all in the same cell.

i.e 1 anystreet anytown zip/postcode,
there are no commas only spaces.

I would like to have first column as house number, next column as street,
next colum as town and final colum as zip/postcode.

One final point is that at the end of each line is about 100 squares. I
thought this would be easy to find and replace with a space but cant even
copy the 'square' into the 'find' to remove all those squares

Hopefully any solution offered will extract just the relevant info and leave
those squares behind.

TIA for all suggestions.

 

[Pete_UK]

You could use Data | Text-to-columns and work through the Wizard to
split the data at each space. If you have a space before those boxes,
then all the boxes will be put into their own cell.

Unfortunately, if you have mixed addresses (some with and some without
house numbers; some streets with one name, others with 2 names; some
with and some without Districts; some towns with one and some with two
words etc., etc.,) then you will find that they don't all occupy the
same number of columns - there is no easy way around this.

Hope this helps.

Pete

 

[bj]

to replace the square
try
= substitute(A1,code(mid(A1,99,1),"")
there may be several different characters which you will have to identify

if all of the towns and streen names are just one word you could try
selecting the column and data-text to column-delimited, select space as a
delimiter.

 

[Teneo]

Hey guys

Data to text function is brill

Its getting better now.

Those squares act as spaces in one area but not in another but the data to
text is helping getting rid.

Thanks