Truncating a text string

In A1 I have the following:


A4 Black
A4 Black+1
A4 1 colour
A4 2 colour
A4 4 colour
A3 Black
A3 Black+1
A3 1 colour
A3 2 colour
A3 4 colour


I want to strip the leading two characters and the first space away leaving
me with:



Black
Black+1
1 colour
2 colour
4 colour
Black
Black+1
1 colour
2 colour
4 colour


I've been fiddling with this for a while but can't get it - I'd appreciate
some help.


Regards,




Gavin

Hopefully you meant that it starts in A1 with A4 Black and A2 has A4 Black+1
and so on, if so you can use a help formula and copy it down alongside

=MID(A1,FIND(" ",A1)+1,255)


Regards,

Peo Sjoblom

"gavin" wrote:

> In A1 I have the following:
>
>
> A4 Black
> A4 Black+1
> A4 1 colour
> A4 2 colour
> A4 4 colour
> A3 Black
> A3 Black+1
> A3 1 colour
> A3 2 colour
> A3 4 colour
>
>
> I want to strip the leading two characters and the first space away leaving
> me with:
>
>
>
> Black
> Black+1
> 1 colour
> 2 colour
> 4 colour
> Black
> Black+1
> 1 colour
> 2 colour
> 4 colour
>
>
> I've been fiddling with this for a while but can't get it - I'd appreciate
> some help.
>
>
> Regards,
>
>
>
>
> Gavin
>
>
>

gavin wrote:

>In A1 I have the following:
>
>
>A4 Black
>A4 Black+1
>A4 1 colour
>A4 2 colour
>A4 4 colour
>A3 Black
>A3 Black+1
>A3 1 colour
>A3 2 colour
>A3 4 colour
>
>
>I want to strip the leading two characters and the first space away leaving
>me with:
>
>
>
>Black
>Black+1
>1 colour
>2 colour
>4 colour
>Black
>Black+1
>1 colour
>2 colour
>4 colour
>
>
>I've been fiddling with this for a while but can't get it - I'd appreciate
>some help.
>
>
>Regards,
>
>
>
>
>Gavin
>
>
---------------------------------------------------------------

To strip off the three left characters try using:

[ ] = RIGHT(A1,LEN(A1)-3)

Bill

Hi Peo,
That's perfect!!! I was messing around with MID and FIND but I didn't quite
fit it all together. I know the "+1" determines the position of the first
character but is "255" just the maximum number of characters that this
function allows?

Your help is much appreciated.


Regards,



Gavin



"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Hopefully you meant that it starts in A1 with A4 Black and A2 has A4
Black+1
> and so on, if so you can use a help formula and copy it down alongside
>
> =MID(A1,FIND(" ",A1)+1,255)
>
>
> Regards,
>
> Peo Sjoblom
>
> "gavin" wrote:
>
> > In A1 I have the following:
> >
> >
> > A4 Black
> > A4 Black+1
> > A4 1 colour
> > A4 2 colour
> > A4 4 colour
> > A3 Black
> > A3 Black+1
> > A3 1 colour
> > A3 2 colour
> > A3 4 colour
> >
> >
> > I want to strip the leading two characters and the first space away
leaving
> > me with:
> >
> >
> >
> > Black
> > Black+1
> > 1 colour
> > 2 colour
> > 4 colour
> > Black
> > Black+1
> > 1 colour
> > 2 colour
> > 4 colour
> >
> >
> > I've been fiddling with this for a while but can't get it - I'd
appreciate
> > some help.
> >
> >
> > Regards,
> >
> >
> >
> >
> > Gavin
> >
> >
> >

Yes a text function like MID can only retrun 255 characters, I believe there
are some workarounds using substitute etc..

Regards,

Peo Sjoblom

"gavin" wrote:

> Hi Peo,
> That's perfect!!! I was messing around with MID and FIND but I didn't quite
> fit it all together. I know the "+1" determines the position of the first
> character but is "255" just the maximum number of characters that this
> function allows?
>
> Your help is much appreciated.
>
>
> Regards,
>
>
>
> Gavin
>
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > Hopefully you meant that it starts in A1 with A4 Black and A2 has A4
> Black+1
> > and so on, if so you can use a help formula and copy it down alongside
> >
> > =MID(A1,FIND(" ",A1)+1,255)
> >
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "gavin" wrote:
> >
> > > In A1 I have the following:
> > >
> > >
> > > A4 Black
> > > A4 Black+1
> > > A4 1 colour
> > > A4 2 colour
> > > A4 4 colour
> > > A3 Black
> > > A3 Black+1
> > > A3 1 colour
> > > A3 2 colour
> > > A3 4 colour
> > >
> > >
> > > I want to strip the leading two characters and the first space away
> leaving
> > > me with:
> > >
> > >
> > >
> > > Black
> > > Black+1
> > > 1 colour
> > > 2 colour
> > > 4 colour
> > > Black
> > > Black+1
> > > 1 colour
> > > 2 colour
> > > 4 colour
> > >
> > >
> > > I've been fiddling with this for a while but can't get it - I'd
> appreciate
> > > some help.
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > >
> > > Gavin
> > >
> > >
> > >
>
>
>

Thanks again, Peo.


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Yes a text function like MID can only retrun 255 characters, I believe
there
> are some workarounds using substitute etc..
>
> Regards,
>
> Peo Sjoblom
>
> "gavin" wrote:
>
> > Hi Peo,
> > That's perfect!!! I was messing around with MID and FIND but I didn't
quite
> > fit it all together. I know the "+1" determines the position of the
first
> > character but is "255" just the maximum number of characters that this
> > function allows?
> >
> > Your help is much appreciated.
> >
> >
> > Regards,
> >
> >
> >
> > Gavin
> >
> >
> >
> > "Peo Sjoblom" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hopefully you meant that it starts in A1 with A4 Black and A2 has A4
> > Black+1
> > > and so on, if so you can use a help formula and copy it down alongside
> > >
> > > =MID(A1,FIND(" ",A1)+1,255)
> > >
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "gavin" wrote:
> > >
> > > > In A1 I have the following:
> > > >
> > > >
> > > > A4 Black
> > > > A4 Black+1
> > > > A4 1 colour
> > > > A4 2 colour
> > > > A4 4 colour
> > > > A3 Black
> > > > A3 Black+1
> > > > A3 1 colour
> > > > A3 2 colour
> > > > A3 4 colour
> > > >
> > > >
> > > > I want to strip the leading two characters and the first space away
> > leaving
> > > > me with:
> > > >
> > > >
> > > >
> > > > Black
> > > > Black+1
> > > > 1 colour
> > > > 2 colour
> > > > 4 colour
> > > > Black
> > > > Black+1
> > > > 1 colour
> > > > 2 colour
> > > > 4 colour
> > > >
> > > >
> > > > I've been fiddling with this for a while but can't get it - I'd
> > appreciate
> > > > some help.
> > > >
> > > >
> > > > Regards,
> > > >
> > > >
> > > >
> > > >
> > > > Gavin
> > > >
> > > >
> > > >
> >
> >
> >

I put this in A1:
=REPT("asdf",5000)
I put this in B1:
=len(a1)

B1 showed:
20000

I put this in a2:
=MID(A1,2,3333)
I put this in B2:
=len(a2)

B2 showed:
3333

I'm betting that Peo just figured that 255 was long enough for your text.



gavin wrote:
>
> Hi Peo,
> That's perfect!!! I was messing around with MID and FIND but I didn't quite
> fit it all together. I know the "+1" determines the position of the first
> character but is "255" just the maximum number of characters that this
> function allows?
>
> Your help is much appreciated.
>
> Regards,
>
> Gavin
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > Hopefully you meant that it starts in A1 with A4 Black and A2 has A4
> Black+1
> > and so on, if so you can use a help formula and copy it down alongside
> >
> > =MID(A1,FIND(" ",A1)+1,255)
> >
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "gavin" wrote:
> >
> > > In A1 I have the following:
> > >
> > >
> > > A4 Black
> > > A4 Black+1
> > > A4 1 colour
> > > A4 2 colour
> > > A4 4 colour
> > > A3 Black
> > > A3 Black+1
> > > A3 1 colour
> > > A3 2 colour
> > > A3 4 colour
> > >
> > >
> > > I want to strip the leading two characters and the first space away
> leaving
> > > me with:
> > >
> > >
> > >
> > > Black
> > > Black+1
> > > 1 colour
> > > 2 colour
> > > 4 colour
> > > Black
> > > Black+1
> > > 1 colour
> > > 2 colour
> > > 4 colour
> > >
> > >
> > > I've been fiddling with this for a while but can't get it - I'd
> appreciate
> > > some help.
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > >
> > > Gavin
> > >
> > >
> > >

--

Dave Peterson

Sorry Gavin, I am wrong (as Dave showed), I probably use 255 because the
column width is 255 characters otherwise you have to turn on wrap text under
format>cells>alignment
Having said that I never have used textstrings even close to that


Regards,

Peo Sjoblom

"gavin" wrote:

> Thanks again, Peo.
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > Yes a text function like MID can only retrun 255 characters, I believe
> there
> > are some workarounds using substitute etc..
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "gavin" wrote:
> >
> > > Hi Peo,
> > > That's perfect!!! I was messing around with MID and FIND but I didn't
> quite
> > > fit it all together. I know the "+1" determines the position of the
> first
> > > character but is "255" just the maximum number of characters that this
> > > function allows?
> > >
> > > Your help is much appreciated.
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > > Gavin
> > >
> > >
> > >
> > > "Peo Sjoblom" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hopefully you meant that it starts in A1 with A4 Black and A2 has A4
> > > Black+1
> > > > and so on, if so you can use a help formula and copy it down alongside
> > > >
> > > > =MID(A1,FIND(" ",A1)+1,255)
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > "gavin" wrote:
> > > >
> > > > > In A1 I have the following:
> > > > >
> > > > >
> > > > > A4 Black
> > > > > A4 Black+1
> > > > > A4 1 colour
> > > > > A4 2 colour
> > > > > A4 4 colour
> > > > > A3 Black
> > > > > A3 Black+1
> > > > > A3 1 colour
> > > > > A3 2 colour
> > > > > A3 4 colour
> > > > >
> > > > >
> > > > > I want to strip the leading two characters and the first space away
> > > leaving
> > > > > me with:
> > > > >
> > > > >
> > > > >
> > > > > Black
> > > > > Black+1
> > > > > 1 colour
> > > > > 2 colour
> > > > > 4 colour
> > > > > Black
> > > > > Black+1
> > > > > 1 colour
> > > > > 2 colour
> > > > > 4 colour
> > > > >
> > > > >
> > > > > I've been fiddling with this for a while but can't get it - I'd
> > > appreciate
> > > > > some help.
> > > > >
> > > > >
> > > > > Regards,
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Gavin
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>