I have a list of addresses with the number and the name in the same column. I want to separate them so I can sort by the name of the street.
Is the format of the cell constant; i.e. something like one or more digits followed by a space followed by anything?
For example
123 Smith Street
15 Open Alley
Or are there other formats such as
Unit 1 97 Some Road
Lot 26 Other Street
PO Box 55 SometownI was able to do this using Find and Replace and Text to Columns.
First, replace the space after the number with another character, like ‘|’ (vertical bar). This will work only if there is exactly one number at the beginning of each address, but will handle numbers elsewhere.
- Select the column
- Ctrl-h or
Edit>Find and Replace... - In the Find box:
^([1-9]+)_replacing the final underscore with a space. - In the Replace box:
$1|exactly - Make sure “Regular Expressions” is checked.
- Press “Replace All”
Next, split the columns.
- Create a blank column to the right of the address column, if necessary.
- Select the address column
-
Data>Text to Columns... - Uncheck “Space” and enter
|in the “Other” box. - Press “OK”
Talking of “the number” and “the name” concerning compound addresses is an excessive euphemism.
@robleyd already listed a few considerate examples reminding of this fact.
Never ever allow compound addresses entrance to a spreadsheet or a database!
Post factum, you have a full-grown case for AI. Just consider the multitude of “traditional” formats…
However, taking the subject literally, the version 6.2 of LibreOffice, already available as 6.2.0.3RC (most likely identical with the release to come soon) is offering a new means to do it: the REGEX() function using the extra parameters. See this attached demo Sheet. The AI part is not included.