Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
Formating a text string? | Excel Discussion (Misc queries) | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |