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,
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." <[email protected]> wrote in message
news:[email protected]...
> 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. <[email protected]>
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks