Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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"