how do I convert cell 'Street,#' to '# Street' these are addresse

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Closing hyperlinked wookbooks upon exit - 30 Apr 2006 02:35 GMT

I have an extensive list of addresses -- provided format is 'streetname,###'
(notice comma), I need addresses in format '### streetname' (notice space).
An example would be 'Washington,1909' and desired outcome would be '1909
Washington' I'd appreciate any suggestions for conversion script.

Thanx RL

Reply to this Message

Gilles Desjardins - 30 Apr 2006 03:32 GMT

First you create an empty column to the right of your text. Then Data, Text
To Columns and follow delimited with coma.
This will split your words from your numbers. At this time you now have two
columns. Then in an ALL new 3rd column to the right you CONCATENATE both
columns.
HTH

Gilles

>I have an extensive list of addresses -- provided format is
>'streetname,###'
[quoted text clipped - 4 lines]
>
> Thanx RL

Reply to this Message

Ron Rosenfeld - 30 Apr 2006 03:37 GMT

>I have an extensive list of addresses -- provided format is 'streetname,###'
>(notice comma), I need addresses in format '### streetname' (notice space).
>An example would be 'Washington,1909' and desired outcome would be '1909
>Washington' I'd appreciate any suggestions for conversion script.
>
>Thanx RL

=RIGHT(A1,LEN(A1)-FIND(",",A1))&" "&LEFT(A1,FIND(",",A1)-1)

--ron

Reply to this Message

daddylonglegs - 30 Apr 2006 11:23 GMT

Here's another option

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

Signature

daddylonglegs

Reply to this Message