How is it possible to split addresses if they download into calc joined together as shown below? Also, how do you get them onto one line, so they appear in the bar at the top?

[gpedlar]

EDIT as preformated text.

47 Winsor Drive
St Leonards HillKentBerkshireBR6 6EYEngland

Morton Peto Road
Harfreys Industrial EstateGreat YarmouthNorfolkNR31 0LT

4 St Georges CottagesWareHertfordshireSG12 7RU

[EarnestAl]

You need to export the addresses from the other software with separators of some sort between the fields. The addresses are of different lengths, one includes country field, the others don’t, some fields have no space others have one. If you have no control over the export then put it into Writer and manually insert a tab between what you think are the different fields and then convert text to table. You don’t want to do it manually so really try hard to export it differently

I don’t understand the second part of your question

[LeroyG]

gpedlar,

“so they appear in the bar at the top”

Do you have many lines with data and wish to show one (selected each time) at the top?

[gabix]

Make the title short.

[gpedlar]

Thanks for the feedback. Much appreciated.

[gpedlar]

Thats right Leroy. Multiple lines, with only the first line appearing in the top bar when highlighted, but i want to see all the data from the cell on one line.

[keme]

If you have no way to force the source to deliver proper separation, there is no certain way to split content without knowledge of possible values. Humans can recognize names. Computer software needs a list, or some rule.

It seems that the entries in your example should be split at points where a lowercase letter is immediately followed by an uppercase letter. This may not be correct for every entry, and indeed it does not successfully separate postcode from country. Still, I propose a procedure to do most of the “dirty work”:

• Select the list of addresses
• Select menu item Edit - Find and replace
• Search for ([:lower:])([:upper:])
• Replace with $1;$2
• Make sure these options are ticked:
• Match case
• Selection only
• Regular expressions
• Click Replace all, then Close
• Menu selection Data - Text to columns...
• Mark Separated by and Semicolon
• Check that the preview looks sensible
• OK

For the task of “appearing in the bar”, I am every bit as lost as @EarnestAl seems to be. Please explain. Assume a dense audience.

[EarnestAl]

I like your explanation @keme, really concise and straightforward. The thing that got me was the lack of consistency, e.g. “47 Winsor Drive” is a street field but there is a space between that and “St Leonards Hill” but not for the next field. Maybe the original database didn’t allow for the extra field needed. I didn’t give it enough thought, sorry.

You can separate the postcodes out beforehand using patterns matching the postcode patterns, I don’t think there are too many postcode patterns.

Edit>> Find and Replace (CTRL+H) and tick Regular Expressions, enter in Find ( [:alpha:][:alpha:][:digit:][:space:][:digit:][:alpha:][:alpha:])

and in Replace enter ;&;

This will put a semicolon between BR6 6EY and the rest of the text in your example so you use the same separators. Repeat for the remaining postcode patterns, e.g. ( [:alpha:][:alpha:][:digit:][:digit:][:space:][:digit:][:alpha:][:alpha:])

[gpedlar]

Much appreciated I will give it a go & come back to you, Graeme.

[gpedlar]

Yes this worked, I am very grateful, thanks !

[gpedlar]

I get some strange results using this

1 AA9A 9AA ([:alpha:][:alpha:][:digit:][:alpha:][:space:][:digit:][:alpha:][:alpha:])
2 A9A 9AA ([:alpha:][:digit:][:alpha:][:space:][:digit:][:alpha:][:alpha:])
3 A9 9AA ([:alpha:][:digit:][:space:][:digit:][:alpha:][:alpha:])
4 A99 9AA ([:alpha:][:digit:][:digit:][:space:][:digit:][:alpha:][:alpha:])
5 AA9 9AA ([:alpha:][:alpha:][:digit:][:space:][:digit:][:alpha:][:alpha:])
6 AA99 9AA ([:alpha:][:alpha:][:digit:][:digit:][:space:][:digit:][:alpha:][:alpha:])

these are the possible postcode arrangements in the UK.

Sometimes the code goes over the pose code again

43 Eagle Street London ;W;C1R 4AT;;
6A Garlinge Road London N;W2 3TR;
38 Glenloch Road London N;W3 4DN;
70 Oakley Square London N;W1 1NJ;
35 Pilgrim’s Lane London N;W3 1SS;

Is ther something to avoid this?

[EarnestAl]

It looks as though No3 [:alpha:][:digit:][:space:][:digit:][:alpha:][:alpha:] is causing the problem. You might need to separate out those addresses from the rest and run the No3 Find and Replace over just those addresses.

[EarnestAl]

How about Find (London)([:alpha:][:digit:][:space:][:digit:][:alpha:][:alpha:]) and Replace $1;$2. I’m assuming that only London has that pattern

[gpedlar]

thanks, i will try out.