It should not be too hard but I am unable to find a solution.

I want to take a specific word from a cell and place it in another.

I have in the cells with addresses eg"1955 BOUNDRY ROAD RD 3 AUCKLAND" and
I wish to split it to three cells, "1955 BOUNDRY ROAD" "RD 3" "AUCKLAND".
If I choose to use the text to colums it puts the data in a seperate cell at
each space.

Please help

In cell A1 enter:
1955 BOUNDRY ROAD RD 3 AUCKLAND
In cell A2 enter:
=LEFT(A1,17)
In cell A3 enter:
=MID(A1,19,4)
In cell A4 enter:
=RIGHT(A1,8)

--
Gary''s Student - gsnu200775


"Jude" wrote:

It should not be too hard but I am unable to find a solution.

I want to take a specific word from a cell and place it in another.

I have in the cells with addresses eg"1955 BOUNDRY ROAD RD 3 AUCKLAND" and
I wish to split it to three cells, "1955 BOUNDRY ROAD" "RD 3" "AUCKLAND".
If I choose to use the text to colums it puts the data in a seperate cell at
each space.

Please help

On Mon, 24 Mar 2008 16:08:01 -0700, Jude
wrote:

It should not be too hard but I am unable to find a solution.

I want to take a specific word from a cell and place it in another.

I have in the cells with addresses eg"1955 BOUNDRY ROAD RD 3 AUCKLAND" and
I wish to split it to three cells, "1955 BOUNDRY ROAD" "RD 3" "AUCKLAND".
If I choose to use the text to colums it puts the data in a seperate cell at
each space.

Please help


What "rules" do you want to use to split up the cell?

Clearly, as you have written, a "rule" that says to split on every space will
not work.

I'm not familiar with NZ addressing formats, but if you could define
unambiguous rules for splitting your data, I or someone will surely be able to
show you how to do it in Excel.
--ron

Which would work if all cells had the same information, but it is all
different :-)

Thanks for the quick responce

"Gary''s Student" wrote:

In cell A1 enter:
1955 BOUNDRY ROAD RD 3 AUCKLAND
In cell A2 enter:
=LEFT(A1,17)
In cell A3 enter:
=MID(A1,19,4)
In cell A4 enter:
=RIGHT(A1,8)

--
Gary''s Student - gsnu200775


"Jude" wrote:

It should not be too hard but I am unable to find a solution.

I want to take a specific word from a cell and place it in another.

I have in the cells with addresses eg"1955 BOUNDRY ROAD RD 3 AUCKLAND" and
I wish to split it to three cells, "1955 BOUNDRY ROAD" "RD 3" "AUCKLAND".
If I choose to use the text to colums it puts the data in a seperate cell at
each space.

Please help

Your address example is six "words" separated by a single space. Therefore
we must find the location of the third and fifth spaces to split the words
correctly:

In cell A1 enter:
1955 BOUNDRY ROAD RD 3 AUCKLAND

In cell A2 enter:
=FIND("^",SUBSTITUTE(A1," ","^",3))
the location of the third space


In cell A3 enter:
=FIND("^",SUBSTITUTE(A1," ","^",5))
the location of the fifth space


In cell A4 enter:
=LEFT(A1,A2-1)
In cell A5 enter:
=MID(A1,A2+1,A3-A2)
In cell A6 enter:
=RIGHT(A1,LEN(A1)-A3)


This will show as:

1955 BOUNDRY ROAD RD 3 AUCKLAND
18
23
1955 BOUNDRY ROAD
RD 3
AUCKLAND

--
Gary''s Student - gsnu200775


"Jude" wrote:

Which would work if all cells had the same information, but it is all
different :-)

Thanks for the quick responce

"Gary''s Student" wrote:

In cell A1 enter:
1955 BOUNDRY ROAD RD 3 AUCKLAND
In cell A2 enter:
=LEFT(A1,17)
In cell A3 enter:
=MID(A1,19,4)
In cell A4 enter:
=RIGHT(A1,8)

--
Gary''s Student - gsnu200775


"Jude" wrote:

It should not be too hard but I am unable to find a solution.

I want to take a specific word from a cell and place it in another.

I have in the cells with addresses eg"1955 BOUNDRY ROAD RD 3 AUCKLAND" and
I wish to split it to three cells, "1955 BOUNDRY ROAD" "RD 3" "AUCKLAND".
If I choose to use the text to colums it puts the data in a seperate cell at
each space.

Please help

Select the cell first and then go to Data| text to column| , choose
Fixed Width option...








On Mar 25, 4:08*am, Jude wrote:
It should not be too hard but I am unable to find a solution. *

I want to take a specific word from a cell and place it in another. *

I have in the cells *with addresses eg"1955 BOUNDRY ROAD RD 3 AUCKLAND" and
I wish to split it to three cells, "1955 BOUNDRY ROAD" *"RD 3" *"AUCKLAND". *
If I choose to use the text to colums it puts the data in a seperate cell at
each space.

Please help