Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem
is, the name won't always be 8 chars long, so is there any way that I can set
it so that it counts all chars of the name until it comes to the space and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for
this one, but again, the chars of this one will differ (eg Claire Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds

Hi

B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3,"")))-1)
C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1)
D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem
is, the name won't always be 8 chars long, so is there any way that I can
set
it so that it counts all chars of the name until it comes to the space and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for
this one, but again, the chars of this one will differ (eg Claire Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds

Hi Avri

Thanks for your quick response, however, the first one (B3) just returns a
blank cell and when I enter the formula in C3 it comes up with an error

Sorry to be a pain, but any ideas?

Linds

"Arvi Laanemets" wrote:

Hi

B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3,"")))-1)
C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1)
D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem
is, the name won't always be 8 chars long, so is there any way that I can
set
it so that it counts all chars of the name until it comes to the space and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for
this one, but again, the chars of this one will differ (eg Claire Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds

Hi

Is the date in A3 remained as text, or did you convert it to date? What does
the formula
=TRIM(SUBSTITUTE($G3,A3,""))
return. It must return "Stevens Feb 22 2005 Lindsey Martin" - when not, then
try with
=TRIM(SUBSTITUTE($G3,TEXT(A3,"dd.mm.yy hh:mm:ss"),""))
or
=TRIM(SUBSTITUTE($G3,TEXT(A3*1,"dd.mm.yy hh:mm:ss"),""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi Avri

Thanks for your quick response, however, the first one (B3) just returns a
blank cell and when I enter the formula in C3 it comes up with an error

Sorry to be a pain, but any ideas?

Linds

"Arvi Laanemets" wrote:

Hi

B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3,"")))-1)
C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1)
D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works,
problem
is, the name won't always be 8 chars long, so is there any way that I
can
set
it so that it counts all chars of the name until it comes to the space
and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT function
for
this one, but again, the chars of this one will differ (eg Claire Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds

Hi Arvi,

The first statement does bring up Stevens Feb 22 2005 Lindsey Martin, so it
must be the rest thats not working.

Cheers
Lindsey

"Arvi Laanemets" wrote:

Hi

Is the date in A3 remained as text, or did you convert it to date? What does
the formula
=TRIM(SUBSTITUTE($G3,A3,""))
return. It must return "Stevens Feb 22 2005 Lindsey Martin" - when not, then
try with
=TRIM(SUBSTITUTE($G3,TEXT(A3,"dd.mm.yy hh:mm:ss"),""))
or
=TRIM(SUBSTITUTE($G3,TEXT(A3*1,"dd.mm.yy hh:mm:ss"),""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi Avri

Thanks for your quick response, however, the first one (B3) just returns a
blank cell and when I enter the formula in C3 it comes up with an error

Sorry to be a pain, but any ideas?

Linds

"Arvi Laanemets" wrote:

Hi

B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3,"")))-1)
C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1)
D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works,
problem
is, the name won't always be 8 chars long, so is there any way that I
can
set
it so that it counts all chars of the name until it comes to the space
and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT function
for
this one, but again, the chars of this one will differ (eg Claire Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds

Hi

So continue with testing stepwise

=FIND(" ",TRIM(SUBSTITUTE($G3,A3,"")))
must return 8. When not, then probably you entered "" as first parameter
instead " ".
When this returns right number, and the whole formula in B3 doesn't return
"Stevens", then you have misspelled something in MID function.

Arvi Laanemets


"Lindsey M" wrote in message
...
Hi Arvi,

The first statement does bring up Stevens Feb 22 2005 Lindsey Martin, so
it
must be the rest thats not working.

Cheers
Lindsey

"Arvi Laanemets" wrote:

Hi

Is the date in A3 remained as text, or did you convert it to date? What
does
the formula
=TRIM(SUBSTITUTE($G3,A3,""))
return. It must return "Stevens Feb 22 2005 Lindsey Martin" - when not,
then
try with
=TRIM(SUBSTITUTE($G3,TEXT(A3,"dd.mm.yy hh:mm:ss"),""))
or
=TRIM(SUBSTITUTE($G3,TEXT(A3*1,"dd.mm.yy hh:mm:ss"),""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi Avri

Thanks for your quick response, however, the first one (B3) just
returns a
blank cell and when I enter the formula in C3 it comes up with an
error

Sorry to be a pain, but any ideas?

Linds

"Arvi Laanemets" wrote:

Hi

B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3,"")))-1)
C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND("
",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1)
D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,""))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Lindsey M" wrote in message
...
Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works,
problem
is, the name won't always be 8 chars long, so is there any way
that I
can
set
it so that it counts all chars of the name until it comes to the
space
and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because
I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT
function
for
this one, but again, the chars of this one will differ (eg Claire
Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds

On Wed, 16 Mar 2005 04:33:01 -0800, Lindsey M
wrote:

Hi

Say I have the following in cell G3:

15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin

I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17)
I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem
is, the name won't always be 8 chars long, so is there any way that I can set
it so that it counts all chars of the name until it comes to the space and
then inserts that?
I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm
assuming that once B3 is sorted, it will work around that?
And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for
this one, but again, the chars of this one will differ (eg Claire Dunn
instead of Lindsey Martin), any ideas on this one?

Any help would be greatly appreciated

Cheers
Linds


A3:

=LEFT(TRIM(G3),FIND(CHAR(1),
SUBSTITUTE(TRIM(G3)," ",CHAR(1),2))-1)

B3:

=MID(TRIM(G3),FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",
CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",
CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",CHAR(1),2))-1)

C3:

=MID(TRIM(G3),FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",
CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",
CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",CHAR(1),3))-1)

D3:

=MID(TRIM(G3),FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",
CHAR(1),6))+1,255)


--ron