Ask Your Question

How can I separate numbers from text in an a column that contains both

asked 2019-02-08 20:26:31 +0100 gravatar image

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.

edit retag flag offensive close merge delete


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 Sometown
robleyd gravatar imagerobleyd ( 2019-02-09 08:05:03 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-02-09 12:39:24 +0100

Lupp gravatar image

updated 2019-02-09 12:41:08 +0100

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

edit flag offensive delete link more

answered 2019-02-09 07:13:44 +0100

KH gravatar image

I 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"
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-08 20:26:31 +0100

Seen: 28 times

Last updated: Feb 09