Hello
I have a string in cell that has 4 words in it.
Like €œxyz abs xyt ttq€�, how can I get each of this value in a different cell
with a formula?
Thanks,

Hi Jim:

There is feature of Excel that will do exactly what you want.

Pull-down Data Text to Columns...
use the delimited option and space as the delimiter

You can change a whole column this way.
--
Gary's Student


"JIM.H." wrote:

Hello
I have a string in cell that has 4 words in it.
Like €œxyz abs xyt ttq€�, how can I get each of this value in a different cell
with a formula?
Thanks,

The easiest way is to use TTC (TextToColumns),
BUT ... if you're asking for a formula because you're going to be perhaps
importing data on a constant basis, and wish to be able to simply paste into
a column and get immediate results, you can try these 2 sets of formulas:

If your data is *exactly* as your posted example, 4 words each containing 3
letters, use (AA).
If your data is 4 words of varying length, use (BB):

Data in Column A,

Enter in B1:
(AA)
=LEFT(A1,3)

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

Enter in C1:
(AA)
=MID(A1,5,3)

(BB)
=MID(A1,LEN(B1)+2,FIND(" ",A1,LEN(B1)+2)-(LEN(B1)+2))

Enter in D1:
(AA)
=MID(A1,9,3)

(BB)
=MID(A1,LEN(B1)+LEN(C1)+3,FIND("
",A1,LEN(B1)+LEN(C1)+3)-(LEN(B1)+LEN(C1)+3))

Enter in E1:
(AA)
=RIGHT(A1,3)

(BB)
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+3))

And select the 4 cells, and copy down as needed.
--
HTH,

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




"JIM.H." wrote in message
...
Hello
I have a string in cell that has 4 words in it.
Like €œxyz abs xyt ttq€�, how can I get each of this value in a different
cell
with a formula?
Thanks,

On Fri, 21 Oct 2005 12:56:02 -0700, JIM.H.
wrote:

Hello
I have a string in cell that has 4 words in it.
Like “xyz abs xyt ttq”, how can I get each of this value in a different cell
with a formula?
Thanks,

As has been previously pointed out, the Text-to-Columns wizard would work well
for this problem. But since you specified formulas, using built in Excel
formulas, with your string in A1:

B1: (first word) =LEFT(A1,FIND(" ",A1)-1)

C1: (second word)

=MID($A$1,1+FIND(CHAR(1),SUBSTITUTE(
$A$1," ",CHAR(1),1)),-1+FIND(CHAR(1),
SUBSTITUTE($A$1," ",CHAR(1),2))-FIND(
CHAR(1),SUBSTITUTE($A$1," ",CHAR(1),1)))

D1: (third word)

=MID($A$1,1+FIND(CHAR(1),SUBSTITUTE(
$A$1," ",CHAR(1),2)),-1+FIND(CHAR(1),
SUBSTITUTE($A$1," ",CHAR(1),3))-FIND(
CHAR(1),SUBSTITUTE($A$1," ",CHAR(1),2)))

E1: (4th word)

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3)))


--ron