ExcelBanter - Truncating a text string

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Truncating a text string (https://www.excelbanter.com/excel-discussion-misc-queries/25563-truncating-text-string.html)

gavin

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

Peo Sjoblom

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

Bill Martin -- (Remove NOSPAM from address)

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

gavin

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" wrote in message
...
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

Peo Sjoblom

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" wrote in message
...
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

Thanks again, Peo.

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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

Peo Sjoblom

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
formatcellsalignment
Having said that I never have used textstrings even close to that

Regards,

Peo Sjoblom

"gavin" wrote:

Thanks again, Peo.

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
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

All times are GMT +1. The time now is 08:19 PM.