Truncating a text string

G

gavin

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
 
P

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
 
G

Guest

gavin said:
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
Click to expand...
---------------------------------------------------------------

To strip off the three left characters try using:

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

Bill
 
G

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
 
P

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
 
G

gavin

Thanks again, Peo.


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

Regards,

Peo Sjoblom
Click to expand...
 
D

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.
 
P

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


Regards,

Peo Sjoblom
 
Top