Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DB4 imported data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DB4 imported data
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 On Aug 16, 7:09 pm, "Sasha" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DB4 imported data
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. "Sasha" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DB4 imported data
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 "bj" wrote in message ... 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. "Sasha" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to graph from an imported data set? | Charts and Charting in Excel | |||
How do I get rid of #REF error, imported Data changes | Excel Worksheet Functions | |||
Imported Data - misinturpretted | Excel Discussion (Misc queries) | |||
Formatting imported data | Excel Worksheet Functions | |||
Templates for Imported data | Excel Worksheet Functions |