Suggestions, please

  • Thread starter Thread starter Jono
  • Start date Start date
J

Jono

Hi all,

I have a spreadsheet with customer names & addresses. Unfortunately, whoever
created it included the house numbers in the same cell/column as the street
names.

As I would like to sort the list in street name (alphabetical) order I need
a way of automating the task of removing the house numbers from the
beginning of the addresses & inserting them into a new column.

Any help would be appreciated.

Thanks.

Jono.
 
Jono,

You can use a formula in another column like
=MID(A1,FIND(" ",A1)+1,LEN(A1))

and then sort by this column.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
That's the fastest response I believe I have ever experienced.thanks.

& it's working!

Wow.

Chip Pearson wrote:
|| Jono,
||
|| You can use a formula in another column like
|| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||
|| and then sort by this column.
||
||
|| --
|| Cordially,
|| Chip Pearson
|| Microsoft MVP - Excel
|| Pearson Software Consulting, LLC
|| www.cpearson.com
||
||
||
||
|| ||| Hi all,
|||
||| I have a spreadsheet with customer names & addresses.
||| Unfortunately, whoever created it included the house numbers in the
||| same cell/column as the street names.
|||
||| As I would like to sort the list in street name (alphabetical)
||| order I need a way of automating the task of removing the house
||| numbers from the beginning of the addresses & inserting them into a
||| new column.
|||
||| Any help would be appreciated.
|||
||| Thanks.
|||
||| Jono.
 
Thanks Chip, that worked beautifully.

I have another question for you, about the same spreadsheet.

How do I now make Excel sort the column with the house numbers & street
names NUMERICALLY?

I'm sorting by the column you helped me create first & then by the original
column, however, the results are ordered 1, 10, 11, 12, 15, 17, 18, 2, 3, 4,
5, 6, 7, 9


Chip Pearson wrote:
|| Jono,
||
|| You can use a formula in another column like
|| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||
|| and then sort by this column.
||
||
|| --
|| Cordially,
|| Chip Pearson
|| Microsoft MVP - Excel
|| Pearson Software Consulting, LLC
|| www.cpearson.com
||
||
||
||
|| ||| Hi all,
|||
||| I have a spreadsheet with customer names & addresses.
||| Unfortunately, whoever created it included the house numbers in the
||| same cell/column as the street names.
|||
||| As I would like to sort the list in street name (alphabetical)
||| order I need a way of automating the task of removing the house
||| numbers from the beginning of the addresses & inserting them into a
||| new column.
|||
||| Any help would be appreciated.
|||
||| Thanks.
|||
||| Jono.
 
Just use another column to extract the text numbers, and convert them to
true numbers for "proper" sorting.

=--LEFT(A1,FIND(" ",A1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Thanks Chip, that worked beautifully.

I have another question for you, about the same spreadsheet.

How do I now make Excel sort the column with the house numbers & street
names NUMERICALLY?

I'm sorting by the column you helped me create first & then by the original
column, however, the results are ordered 1, 10, 11, 12, 15, 17, 18, 2, 3, 4,
5, 6, 7, 9


Chip Pearson wrote:
|| Jono,
||
|| You can use a formula in another column like
|| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||
|| and then sort by this column.
||
||
|| --
|| Cordially,
|| Chip Pearson
|| Microsoft MVP - Excel
|| Pearson Software Consulting, LLC
|| www.cpearson.com
||
||
||
||
|| ||| Hi all,
|||
||| I have a spreadsheet with customer names & addresses.
||| Unfortunately, whoever created it included the house numbers in the
||| same cell/column as the street names.
|||
||| As I would like to sort the list in street name (alphabetical)
||| order I need a way of automating the task of removing the house
||| numbers from the beginning of the addresses & inserting them into a
||| new column.
|||
||| Any help would be appreciated.
|||
||| Thanks.
|||
||| Jono.
 
Thanks RagDyer,

Unfortunately, the suggestion appears to return a value of 1, rather than
the actual house number.

What may I be doing wrong? I suspect I don't know quite enough!

RagDyer wrote:
|| Just use another column to extract the text numbers, and convert
|| them to true numbers for "proper" sorting.
||
|| =--LEFT(A1,FIND(" ",A1))
|| --
||
|| HTH,
||
|| RD
|| ==============================================
|| Please keep all correspondence within the Group, so all may benefit!
|| ==============================================
||
||
||
|| || Thanks Chip, that worked beautifully.
||
|| I have another question for you, about the same spreadsheet.
||
|| How do I now make Excel sort the column with the house numbers &
|| street names NUMERICALLY?
||
|| I'm sorting by the column you helped me create first & then by the
|| original column, however, the results are ordered 1, 10, 11, 12, 15,
|| 17, 18, 2, 3, 4, 5, 6, 7, 9
||
||
|| Chip Pearson wrote:
|||| Jono,
||||
|||| You can use a formula in another column like
|||| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||||
|||| and then sort by this column.
||||
||||
|||| --
|||| Cordially,
|||| Chip Pearson
|||| Microsoft MVP - Excel
|||| Pearson Software Consulting, LLC
|||| www.cpearson.com
||||
||||
||||
||||
|||| ||||| Hi all,
|||||
||||| I have a spreadsheet with customer names & addresses.
||||| Unfortunately, whoever created it included the house numbers in
||||| the same cell/column as the street names.
|||||
||||| As I would like to sort the list in street name (alphabetical)
||||| order I need a way of automating the task of removing the house
||||| numbers from the beginning of the addresses & inserting them into
||||| a
||||| new column.
|||||
||||| Any help would be appreciated.
|||||
||||| Thanks.
|||||
||||| Jono.
 
If the house number is in this form:

123 Any Street

Then the formula should work!

Is this the configuration that you're using?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Thanks RagDyer,

Unfortunately, the suggestion appears to return a value of 1, rather than
the actual house number.

What may I be doing wrong? I suspect I don't know quite enough!

RagDyer wrote:
|| Just use another column to extract the text numbers, and convert
|| them to true numbers for "proper" sorting.
||
|| =--LEFT(A1,FIND(" ",A1))
|| --
||
|| HTH,
||
|| RD
|| ==============================================
|| Please keep all correspondence within the Group, so all may benefit!
|| ==============================================
||
||
||
|| || Thanks Chip, that worked beautifully.
||
|| I have another question for you, about the same spreadsheet.
||
|| How do I now make Excel sort the column with the house numbers &
|| street names NUMERICALLY?
||
|| I'm sorting by the column you helped me create first & then by the
|| original column, however, the results are ordered 1, 10, 11, 12, 15,
|| 17, 18, 2, 3, 4, 5, 6, 7, 9
||
||
|| Chip Pearson wrote:
|||| Jono,
||||
|||| You can use a formula in another column like
|||| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||||
|||| and then sort by this column.
||||
||||
|||| --
|||| Cordially,
|||| Chip Pearson
|||| Microsoft MVP - Excel
|||| Pearson Software Consulting, LLC
|||| www.cpearson.com
||||
||||
||||
||||
|||| ||||| Hi all,
|||||
||||| I have a spreadsheet with customer names & addresses.
||||| Unfortunately, whoever created it included the house numbers in
||||| the same cell/column as the street names.
|||||
||||| As I would like to sort the list in street name (alphabetical)
||||| order I need a way of automating the task of removing the house
||||| numbers from the beginning of the addresses & inserting them into
||||| a
||||| new column.
|||||
||||| Any help would be appreciated.
|||||
||||| Thanks.
|||||
||||| Jono.
 
Although it probably should be

=--LEFT(A1,FIND(" ",a1)-1) to avoid a trailing space.

Alan Beban
 
Check it out Alan, the unary eliminates it automatically.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Although it probably should be

=--LEFT(A1,FIND(" ",a1)-1) to avoid a trailing space.

Alan Beban
 
Something else that you could try, that can be completed in one procedure,
is to use TextToColumns.

Addresses in column A,
Select the address column, and then:
<Data> <TextToColumns>
Delimited should be checked, then <Next>
Click in "Space", then <Next>.

You now see how the column will be separated, and you'll see that they're
all formatted as General, so that your numbers will sort "properly".

Now, to retain your original address column without changing, and still
obtain this separation, you must change the "Destination" cell to *other*
then your original column, say B1, making sure that there are enough empty
columns adjoining, in order to accommodate the niumber of columns that your
addresses have been broken up into.

Then click <Finish>, and you have your original address column, together
with the 2 or 3 or 4 separated columns, which you can use as sort keys to
obtain any order that you desire.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


If the house number is in this form:

123 Any Street

Then the formula should work!

Is this the configuration that you're using?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Thanks RagDyer,

Unfortunately, the suggestion appears to return a value of 1, rather than
the actual house number.

What may I be doing wrong? I suspect I don't know quite enough!

RagDyer wrote:
|| Just use another column to extract the text numbers, and convert
|| them to true numbers for "proper" sorting.
||
|| =--LEFT(A1,FIND(" ",A1))
|| --
||
|| HTH,
||
|| RD
|| ==============================================
|| Please keep all correspondence within the Group, so all may benefit!
|| ==============================================
||
||
||
|| || Thanks Chip, that worked beautifully.
||
|| I have another question for you, about the same spreadsheet.
||
|| How do I now make Excel sort the column with the house numbers &
|| street names NUMERICALLY?
||
|| I'm sorting by the column you helped me create first & then by the
|| original column, however, the results are ordered 1, 10, 11, 12, 15,
|| 17, 18, 2, 3, 4, 5, 6, 7, 9
||
||
|| Chip Pearson wrote:
|||| Jono,
||||
|||| You can use a formula in another column like
|||| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||||
|||| and then sort by this column.
||||
||||
|||| --
|||| Cordially,
|||| Chip Pearson
|||| Microsoft MVP - Excel
|||| Pearson Software Consulting, LLC
|||| www.cpearson.com
||||
||||
||||
||||
|||| ||||| Hi all,
|||||
||||| I have a spreadsheet with customer names & addresses.
||||| Unfortunately, whoever created it included the house numbers in
||||| the same cell/column as the street names.
|||||
||||| As I would like to sort the list in street name (alphabetical)
||||| order I need a way of automating the task of removing the house
||||| numbers from the beginning of the addresses & inserting them into
||||| a
||||| new column.
|||||
||||| Any help would be appreciated.
|||||
||||| Thanks.
|||||
||||| Jono.
 
thanks - got it working.

I ended up using =LEFT(A1,FIND(" ",A1))

What difference would the -- after = make?

RagDyer wrote:
|| If the house number is in this form:
||
|| 123 Any Street
||
|| Then the formula should work!
||
|| Is this the configuration that you're using?
|| --
||
||
|| Regards,
||
|| RD
|| --------------------------------------------------------------------
|| Please keep all correspondence within the Group, so all may benefit!
|| -------------------------------------------------------------------
||
|| || Thanks RagDyer,
||
|| Unfortunately, the suggestion appears to return a value of 1, rather
|| than the actual house number.
||
|| What may I be doing wrong? I suspect I don't know quite enough!
||
|| RagDyer wrote:
|||| Just use another column to extract the text numbers, and convert
|||| them to true numbers for "proper" sorting.
||||
|||| =--LEFT(A1,FIND(" ",A1))
|||| --
||||
|||| HTH,
||||
|||| RD
|||| ==============================================
|||| Please keep all correspondence within the Group, so all may
|||| benefit! ==============================================
||||
||||
||||
|||| |||| Thanks Chip, that worked beautifully.
||||
|||| I have another question for you, about the same spreadsheet.
||||
|||| How do I now make Excel sort the column with the house numbers &
|||| street names NUMERICALLY?
||||
|||| I'm sorting by the column you helped me create first & then by the
|||| original column, however, the results are ordered 1, 10, 11, 12,
|||| 15, 17, 18, 2, 3, 4, 5, 6, 7, 9
||||
||||
|||| Chip Pearson wrote:
|||||| Jono,
||||||
|||||| You can use a formula in another column like
|||||| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||||||
|||||| and then sort by this column.
||||||
||||||
|||||| --
|||||| Cordially,
|||||| Chip Pearson
|||||| Microsoft MVP - Excel
|||||| Pearson Software Consulting, LLC
|||||| www.cpearson.com
||||||
||||||
||||||
||||||
|||||| ||||||| Hi all,
|||||||
||||||| I have a spreadsheet with customer names & addresses.
||||||| Unfortunately, whoever created it included the house numbers in
||||||| the same cell/column as the street names.
|||||||
||||||| As I would like to sort the list in street name (alphabetical)
||||||| order I need a way of automating the task of removing the house
||||||| numbers from the beginning of the addresses & inserting them
||||||| into a
||||||| new column.
|||||||
||||||| Any help would be appreciated.
|||||||
||||||| Thanks.
|||||||
||||||| Jono.
 
Thanks for your suggestion.

Alan Beban wrote:
|| Although it probably should be
||
|| =--LEFT(A1,FIND(" ",a1)-1) to avoid a trailing space.
||
|| Alan Beban
||
|| RagDyer wrote:
||| If the house number is in this form:
|||
||| 123 Any Street
|||
||| Then the formula should work!
|||
||| Is this the configuration that you're using?
 
The results of the formula that you used are *Text*.

Have you tried sorting on them?
They should give you the same "improper" sort that you had at the outset!

The double minuses (unary), change the text numbers to real numbers, which
should sort *numerically*, not as text.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
thanks - got it working.

I ended up using =LEFT(A1,FIND(" ",A1))

What difference would the -- after = make?

RagDyer wrote:
|| If the house number is in this form:
||
|| 123 Any Street
||
|| Then the formula should work!
||
|| Is this the configuration that you're using?
|| --
||
||
|| Regards,
||
|| RD
|| --------------------------------------------------------------------
|| Please keep all correspondence within the Group, so all may benefit!
|| -------------------------------------------------------------------
||
|| || Thanks RagDyer,
||
|| Unfortunately, the suggestion appears to return a value of 1, rather
|| than the actual house number.
||
|| What may I be doing wrong? I suspect I don't know quite enough!
||
|| RagDyer wrote:
|||| Just use another column to extract the text numbers, and convert
|||| them to true numbers for "proper" sorting.
||||
|||| =--LEFT(A1,FIND(" ",A1))
|||| --
||||
|||| HTH,
||||
|||| RD
|||| ==============================================
|||| Please keep all correspondence within the Group, so all may
|||| benefit! ==============================================
||||
||||
||||
|||| |||| Thanks Chip, that worked beautifully.
||||
|||| I have another question for you, about the same spreadsheet.
||||
|||| How do I now make Excel sort the column with the house numbers &
|||| street names NUMERICALLY?
||||
|||| I'm sorting by the column you helped me create first & then by the
|||| original column, however, the results are ordered 1, 10, 11, 12,
|||| 15, 17, 18, 2, 3, 4, 5, 6, 7, 9
||||
||||
|||| Chip Pearson wrote:
|||||| Jono,
||||||
|||||| You can use a formula in another column like
|||||| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||||||
|||||| and then sort by this column.
||||||
||||||
|||||| --
|||||| Cordially,
|||||| Chip Pearson
|||||| Microsoft MVP - Excel
|||||| Pearson Software Consulting, LLC
|||||| www.cpearson.com
||||||
||||||
||||||
||||||
|||||| ||||||| Hi all,
|||||||
||||||| I have a spreadsheet with customer names & addresses.
||||||| Unfortunately, whoever created it included the house numbers in
||||||| the same cell/column as the street names.
|||||||
||||||| As I would like to sort the list in street name (alphabetical)
||||||| order I need a way of automating the task of removing the house
||||||| numbers from the beginning of the addresses & inserting them
||||||| into a
||||||| new column.
|||||||
||||||| Any help would be appreciated.
|||||||
||||||| Thanks.
|||||||
||||||| Jono.
 
Thanks for the clarification.

Suprisingly, the sort is now perfect, without using the --

Cheers

RagDyer wrote:
|| The results of the formula that you used are *Text*.
||
|| Have you tried sorting on them?
|| They should give you the same "improper" sort that you had at the
|| outset!
||
|| The double minuses (unary), change the text numbers to real numbers,
|| which should sort *numerically*, not as text.
||
|| --
||
|| Regards,
||
|| RD
|| --------------------------------------------------------------------
|| Please keep all correspondence within the Group, so all may benefit!
|| -------------------------------------------------------------------
|| || thanks - got it working.
||
|| I ended up using =LEFT(A1,FIND(" ",A1))
||
|| What difference would the -- after = make?
||
|| RagDyer wrote:
|||| If the house number is in this form:
||||
|||| 123 Any Street
||||
|||| Then the formula should work!
||||
|||| Is this the configuration that you're using?
|||| --
||||
||||
|||| Regards,
||||
|||| RD
|||| --------------------------------------------------------------------
|||| Please keep all correspondence within the Group, so all may
|||| benefit!
|||| -------------------------------------------------------------------
||||
|||| |||| Thanks RagDyer,
||||
|||| Unfortunately, the suggestion appears to return a value of 1,
|||| rather than the actual house number.
||||
|||| What may I be doing wrong? I suspect I don't know quite enough!
||||
|||| RagDyer wrote:
|||||| Just use another column to extract the text numbers, and convert
|||||| them to true numbers for "proper" sorting.
||||||
|||||| =--LEFT(A1,FIND(" ",A1))
|||||| --
||||||
|||||| HTH,
||||||
|||||| RD
|||||| ==============================================
|||||| Please keep all correspondence within the Group, so all may
|||||| benefit! ==============================================
||||||
||||||
||||||
|||||| |||||| Thanks Chip, that worked beautifully.
||||||
|||||| I have another question for you, about the same spreadsheet.
||||||
|||||| How do I now make Excel sort the column with the house numbers &
|||||| street names NUMERICALLY?
||||||
|||||| I'm sorting by the column you helped me create first & then by
|||||| the original column, however, the results are ordered 1, 10, 11,
|||||| 12, 15, 17, 18, 2, 3, 4, 5, 6, 7, 9
||||||
||||||
|||||| Chip Pearson wrote:
|||||||| Jono,
||||||||
|||||||| You can use a formula in another column like
|||||||| =MID(A1,FIND(" ",A1)+1,LEN(A1))
||||||||
|||||||| and then sort by this column.
||||||||
||||||||
|||||||| --
|||||||| Cordially,
|||||||| Chip Pearson
|||||||| Microsoft MVP - Excel
|||||||| Pearson Software Consulting, LLC
|||||||| www.cpearson.com
||||||||
||||||||
||||||||
||||||||
|||||||| ||||||||| Hi all,
|||||||||
||||||||| I have a spreadsheet with customer names & addresses.
||||||||| Unfortunately, whoever created it included the house numbers
||||||||| in the same cell/column as the street names.
|||||||||
||||||||| As I would like to sort the list in street name (alphabetical)
||||||||| order I need a way of automating the task of removing the
||||||||| house numbers from the beginning of the addresses & inserting
||||||||| them
||||||||| into a
||||||||| new column.
|||||||||
||||||||| Any help would be appreciated.
|||||||||
||||||||| Thanks.
|||||||||
||||||||| Jono.
 
Back
Top