LEFT, MID functions?

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<at>tarkon.ee
Arvi Laanemets


"Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
news:98ACDBAD-C25E-4F4E-933A-4F02C0A9692F@microsoft.com...
> 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<at>tarkon.ee
> Arvi Laanemets
>
>
> "Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
> news:98ACDBAD-C25E-4F4E-933A-4F02C0A9692F@microsoft.com...
> > 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<at>tarkon.ee
Arvi Laanemets


"Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
news:BDE28CC3-33E4-4CC8-B328-B8EB580D14F6@microsoft.com...
> 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<at>tarkon.ee
> > Arvi Laanemets
> >
> >
> > "Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
> > news:98ACDBAD-C25E-4F4E-933A-4F02C0A9692F@microsoft.com...
> > > 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<at>tarkon.ee
> Arvi Laanemets
>
>
> "Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
> news:BDE28CC3-33E4-4CC8-B328-B8EB580D14F6@microsoft.com...
> > 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<at>tarkon.ee
> > > Arvi Laanemets
> > >
> > >
> > > "Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
> > > news:98ACDBAD-C25E-4F4E-933A-4F02C0A9692F@microsoft.com...
> > > > 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" <LindseyM@discussions.microsoft.com> wrote in message
news:5D495B4E-A1D3-4655-AD1A-7B52BCF1A8BF@microsoft.com...
> 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<at>tarkon.ee
> > Arvi Laanemets
> >
> >
> > "Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
> > news:BDE28CC3-33E4-4CC8-B328-B8EB580D14F6@microsoft.com...
> > > 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<at>tarkon.ee
> > > > Arvi Laanemets
> > > >
> > > >
> > > > "Lindsey M" <LindseyM@discussions.microsoft.com> wrote in message
> > > > news:98ACDBAD-C25E-4F4E-933A-4F02C0A9692F@microsoft.com...
> > > > > 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
<LindseyM@discussions.microsoft.com> 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