truncating text strings of different lengths

Funkey · 03-29-2006, 10:10 AM

I have a lot of text data in a column. The text is different lengths.(see
below). I want to remove the last letter. I cannot use text to columns
because it left justifies(is there some way to right justify). I tries left
and right functions but becuase the text is different lengths (some 5, 6,7,
10 characters etc.). this does not work. I looked at truncate but it is
text not numbers to this does not work.
Finally I thought of Find and Replace but because the letter may be in the
body of the text as well as a suffix, this does not work. It cannot be done
by hand accurately becasue I have thousands of items. Any Ideas????

Thanks for taking time to look at this. I am trying to strip off the last
one or
two characters from the column B list. Since the text is different length
it
cannot be done with text to colums -this uses a left justify. I tried right
and left functions
but again with no luck. The find & replace won"t work because the same
letter sometimes
is in the number as well as at the end. There are too many to do it by hand.
Any ideas?

Original Corrected

SYJ600002 SYJ600002
SYJ600002 SYJ600002
SYJ6014004 SYJ6014004
M680972 M680972
M580619T M580619 Remomve T
SYJ600002 SYJ600002
SYJ600002 SYJ600002
SYJ6000001 SYJ6000001
SYJ6000001 SYJ6000001
SYJ6009004 SYJ6009004
SYJ6009010 SYJ6009010
226540A 226540 Remove A
SYJ6011003E SYJ6011003 Remove E
404881A 404881 Remove A
SY600000071 SY600000071
SYN50479B SYN50479 Remove B
CSQ060059 CSQ060059
SYJ6030014 SYJ6030014
T4436605 T4436605
T4436605 T4436605
BO4K440674C BO4K440674 Remove C
405247A 405247 Remove A
SYJ600002 SYJ600002
SYJ6014004 SYJ6014004
SYJ6014004 SYJ6014004
SYJ6026001 SYJ6026001
SYJ6000001 SYJ6000001
SYJ6011003G SYJ6011003 Remove G
405464A 405464 Remove A
SYJ6026002 SYJ6026002
CSYN50003 CSYN50003
CSYN50003 CSYN50003

Duke Carey · 03-29-2006, 10:30 AM

Try:

=left(A1,len(A1)-1)

You might need to use

=left(trim(a1),len(trim(a1))-1)

if there are any spaces in the cell in addition to the characters

"Funkey" wrote:

> I have a lot of text data in a column. The text is different lengths.(see
> below). I want to remove the last letter. I cannot use text to columns
> because it left justifies(is there some way to right justify). I tries left
> and right functions but becuase the text is different lengths (some 5, 6,7,
> 10 characters etc.). this does not work. I looked at truncate but it is
> text not numbers to this does not work.
> Finally I thought of Find and Replace but because the letter may be in the
> body of the text as well as a suffix, this does not work. It cannot be done
> by hand accurately becasue I have thousands of items. Any Ideas????
>
>
> Thanks for taking time to look at this. I am trying to strip off the last
> one or
> two characters from the column B list. Since the text is different length
> it
> cannot be done with text to colums -this uses a left justify. I tried right
> and left functions
> but again with no luck. The find & replace won"t work because the same
> letter sometimes
> is in the number as well as at the end. There are too many to do it by hand.
> Any ideas?
>
>
> Original Corrected
>
> SYJ600002 SYJ600002
> SYJ600002 SYJ600002
> SYJ6014004 SYJ6014004
> M680972 M680972
> M580619T M580619 Remomve T
> SYJ600002 SYJ600002
> SYJ600002 SYJ600002
> SYJ6000001 SYJ6000001
> SYJ6000001 SYJ6000001
> SYJ6009004 SYJ6009004
> SYJ6009010 SYJ6009010
> 226540A 226540 Remove A
> SYJ6011003E SYJ6011003 Remove E
> 404881A 404881 Remove A
> SY600000071 SY600000071
> SYN50479B SYN50479 Remove B
> CSQ060059 CSQ060059
> SYJ6030014 SYJ6030014
> T4436605 T4436605
> T4436605 T4436605
> BO4K440674C BO4K440674 Remove C
> 405247A 405247 Remove A
> SYJ600002 SYJ600002
> SYJ6014004 SYJ6014004
> SYJ6014004 SYJ6014004
> SYJ6026001 SYJ6026001
> SYJ6000001 SYJ6000001
> SYJ6011003G SYJ6011003 Remove G
> 405464A 405464 Remove A
> SYJ6026002 SYJ6026002
> CSYN50003 CSYN50003
> CSYN50003 CSYN50003
>
>
>
>
>

Dave Peterson · 03-29-2006, 10:30 AM

Can you use a helper column of formulas that look like this:

=IF(A1="","",IF(ISNUMBER(-RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1)))

Funkey wrote:
>
> I have a lot of text data in a column. The text is different lengths.(see
> below). I want to remove the last letter. I cannot use text to columns
> because it left justifies(is there some way to right justify). I tries left
> and right functions but becuase the text is different lengths (some 5, 6,7,
> 10 characters etc.). this does not work. I looked at truncate but it is
> text not numbers to this does not work.
> Finally I thought of Find and Replace but because the letter may be in the
> body of the text as well as a suffix, this does not work. It cannot be done
> by hand accurately becasue I have thousands of items. Any Ideas????
>
> Thanks for taking time to look at this. I am trying to strip off the last
> one or
> two characters from the column B list. Since the text is different length
> it
> cannot be done with text to colums -this uses a left justify. I tried right
> and left functions
> but again with no luck. The find & replace won"t work because the same
> letter sometimes
> is in the number as well as at the end. There are too many to do it by hand.
> Any ideas?
>
>
> Original Corrected
>
> SYJ600002 SYJ600002
> SYJ600002 SYJ600002
> SYJ6014004 SYJ6014004
> M680972 M680972
> M580619T M580619 Remomve T
> SYJ600002 SYJ600002
> SYJ600002 SYJ600002
> SYJ6000001 SYJ6000001
> SYJ6000001 SYJ6000001
> SYJ6009004 SYJ6009004
> SYJ6009010 SYJ6009010
> 226540A 226540 Remove A
> SYJ6011003E SYJ6011003 Remove E
> 404881A 404881 Remove A
> SY600000071 SY600000071
> SYN50479B SYN50479 Remove B
> CSQ060059 CSQ060059
> SYJ6030014 SYJ6030014
> T4436605 T4436605
> T4436605 T4436605
> BO4K440674C BO4K440674 Remove C
> 405247A 405247 Remove A
> SYJ600002 SYJ600002
> SYJ6014004 SYJ6014004
> SYJ6014004 SYJ6014004
> SYJ6026001 SYJ6026001
> SYJ6000001 SYJ6000001
> SYJ6011003G SYJ6011003 Remove G
> 405464A 405464 Remove A
> SYJ6026002 SYJ6026002
> CSYN50003 CSYN50003
> CSYN50003 CSYN50003

--

Dave Peterson

Kevin B · 03-29-2006, 10:35 AM

Insert a helper column to the right of the column containing your text string
and enter the following formula:

=IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))

The formula example starts in B1, and references the data in A1.

If the last character in A1 is a number, use the current value, otherwise
extract the number of characters from the value that is the value of its
length minus 1

--
Kevin Backmann

"Funkey" wrote:

> I have a lot of text data in a column. The text is different lengths.(see
> below). I want to remove the last letter. I cannot use text to columns
> because it left justifies(is there some way to right justify). I tries left
> and right functions but becuase the text is different lengths (some 5, 6,7,
> 10 characters etc.). this does not work. I looked at truncate but it is
> text not numbers to this does not work.
> Finally I thought of Find and Replace but because the letter may be in the
> body of the text as well as a suffix, this does not work. It cannot be done
> by hand accurately becasue I have thousands of items. Any Ideas????
>
>
> Thanks for taking time to look at this. I am trying to strip off the last
> one or
> two characters from the column B list. Since the text is different length
> it
> cannot be done with text to colums -this uses a left justify. I tried right
> and left functions
> but again with no luck. The find & replace won"t work because the same
> letter sometimes
> is in the number as well as at the end. There are too many to do it by hand.
> Any ideas?
>
>
> Original Corrected
>
> SYJ600002 SYJ600002
> SYJ600002 SYJ600002
> SYJ6014004 SYJ6014004
> M680972 M680972
> M580619T M580619 Remomve T
> SYJ600002 SYJ600002
> SYJ600002 SYJ600002
> SYJ6000001 SYJ6000001
> SYJ6000001 SYJ6000001
> SYJ6009004 SYJ6009004
> SYJ6009010 SYJ6009010
> 226540A 226540 Remove A
> SYJ6011003E SYJ6011003 Remove E
> 404881A 404881 Remove A
> SY600000071 SY600000071
> SYN50479B SYN50479 Remove B
> CSQ060059 CSQ060059
> SYJ6030014 SYJ6030014
> T4436605 T4436605
> T4436605 T4436605
> BO4K440674C BO4K440674 Remove C
> 405247A 405247 Remove A
> SYJ600002 SYJ600002
> SYJ6014004 SYJ6014004
> SYJ6014004 SYJ6014004
> SYJ6026001 SYJ6026001
> SYJ6000001 SYJ6000001
> SYJ6011003G SYJ6011003 Remove G
> 405464A 405464 Remove A
> SYJ6026002 SYJ6026002
> CSYN50003 CSYN50003
> CSYN50003 CSYN50003
>
>
>
>
>

Dave Peterson · 03-29-2006, 10:40 AM

=right() always returns text.
So =isnumber(right()) will always be false

You could use:
=IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,LEFT(A1,LEN(A1)-1))
though.

Kevin B wrote:
>
> Insert a helper column to the right of the column containing your text string
> and enter the following formula:
>
> =IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))
>
> The formula example starts in B1, and references the data in A1.
>
> If the last character in A1 is a number, use the current value, otherwise
> extract the number of characters from the value that is the value of its
> length minus 1
>
> --
> Kevin Backmann
>
> "Funkey" wrote:
>
> > I have a lot of text data in a column. The text is different lengths.(see
> > below). I want to remove the last letter. I cannot use text to columns
> > because it left justifies(is there some way to right justify). I tries left
> > and right functions but becuase the text is different lengths (some 5, 6,7,
> > 10 characters etc.). this does not work. I looked at truncate but it is
> > text not numbers to this does not work.
> > Finally I thought of Find and Replace but because the letter may be in the
> > body of the text as well as a suffix, this does not work. It cannot be done
> > by hand accurately becasue I have thousands of items. Any Ideas????
> >
> >
> > Thanks for taking time to look at this. I am trying to strip off the last
> > one or
> > two characters from the column B list. Since the text is different length
> > it
> > cannot be done with text to colums -this uses a left justify. I tried right
> > and left functions
> > but again with no luck. The find & replace won"t work because the same
> > letter sometimes
> > is in the number as well as at the end. There are too many to do it by hand.
> > Any ideas?
> >
> >
> > Original Corrected
> >
> > SYJ600002 SYJ600002
> > SYJ600002 SYJ600002
> > SYJ6014004 SYJ6014004
> > M680972 M680972
> > M580619T M580619 Remomve T
> > SYJ600002 SYJ600002
> > SYJ600002 SYJ600002
> > SYJ6000001 SYJ6000001
> > SYJ6000001 SYJ6000001
> > SYJ6009004 SYJ6009004
> > SYJ6009010 SYJ6009010
> > 226540A 226540 Remove A
> > SYJ6011003E SYJ6011003 Remove E
> > 404881A 404881 Remove A
> > SY600000071 SY600000071
> > SYN50479B SYN50479 Remove B
> > CSQ060059 CSQ060059
> > SYJ6030014 SYJ6030014
> > T4436605 T4436605
> > T4436605 T4436605
> > BO4K440674C BO4K440674 Remove C
> > 405247A 405247 Remove A
> > SYJ600002 SYJ600002
> > SYJ6014004 SYJ6014004
> > SYJ6014004 SYJ6014004
> > SYJ6026001 SYJ6026001
> > SYJ6000001 SYJ6000001
> > SYJ6011003G SYJ6011003 Remove G
> > 405464A 405464 Remove A
> > SYJ6026002 SYJ6026002
> > CSYN50003 CSYN50003
> > CSYN50003 CSYN50003
> >
> >
> >
> >
> >

--

Dave Peterson

Kevin B · 03-29-2006, 10:50 AM

I was dead wrong on that one, thanks for the correction Dave.
--
Kevin Backmann

"Dave Peterson" wrote:

> =right() always returns text.
> So =isnumber(right()) will always be false
>
> You could use:
> =IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,LEFT(A1,LEN(A1)-1))
> though.
>
> Kevin B wrote:
> >
> > Insert a helper column to the right of the column containing your text string
> > and enter the following formula:
> >
> > =IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))
> >
> > The formula example starts in B1, and references the data in A1.
> >
> > If the last character in A1 is a number, use the current value, otherwise
> > extract the number of characters from the value that is the value of its
> > length minus 1
> >
> > --
> > Kevin Backmann
> >
> > "Funkey" wrote:
> >
> > > I have a lot of text data in a column. The text is different lengths.(see
> > > below). I want to remove the last letter. I cannot use text to columns
> > > because it left justifies(is there some way to right justify). I tries left
> > > and right functions but becuase the text is different lengths (some 5, 6,7,
> > > 10 characters etc.). this does not work. I looked at truncate but it is
> > > text not numbers to this does not work.
> > > Finally I thought of Find and Replace but because the letter may be in the
> > > body of the text as well as a suffix, this does not work. It cannot be done
> > > by hand accurately becasue I have thousands of items. Any Ideas????
> > >
> > >
> > > Thanks for taking time to look at this. I am trying to strip off the last
> > > one or
> > > two characters from the column B list. Since the text is different length
> > > it
> > > cannot be done with text to colums -this uses a left justify. I tried right
> > > and left functions
> > > but again with no luck. The find & replace won"t work because the same
> > > letter sometimes
> > > is in the number as well as at the end. There are too many to do it by hand.
> > > Any ideas?
> > >
> > >
> > > Original Corrected
> > >
> > > SYJ600002 SYJ600002
> > > SYJ600002 SYJ600002
> > > SYJ6014004 SYJ6014004
> > > M680972 M680972
> > > M580619T M580619 Remomve T
> > > SYJ600002 SYJ600002
> > > SYJ600002 SYJ600002
> > > SYJ6000001 SYJ6000001
> > > SYJ6000001 SYJ6000001
> > > SYJ6009004 SYJ6009004
> > > SYJ6009010 SYJ6009010
> > > 226540A 226540 Remove A
> > > SYJ6011003E SYJ6011003 Remove E
> > > 404881A 404881 Remove A
> > > SY600000071 SY600000071
> > > SYN50479B SYN50479 Remove B
> > > CSQ060059 CSQ060059
> > > SYJ6030014 SYJ6030014
> > > T4436605 T4436605
> > > T4436605 T4436605
> > > BO4K440674C BO4K440674 Remove C
> > > 405247A 405247 Remove A
> > > SYJ600002 SYJ600002
> > > SYJ6014004 SYJ6014004
> > > SYJ6014004 SYJ6014004
> > > SYJ6026001 SYJ6026001
> > > SYJ6000001 SYJ6000001
> > > SYJ6011003G SYJ6011003 Remove G
> > > 405464A 405464 Remove A
> > > SYJ6026002 SYJ6026002
> > > CSYN50003 CSYN50003
> > > CSYN50003 CSYN50003
> > >
> > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

Funkey · 03-29-2006, 01:40 PM

Dave

This will work if the text is right justified and there are no spaces after
the text.
You guys are good!!!!

Thanks a million

"Dave Peterson" wrote:

> =right() always returns text.
> So =isnumber(right()) will always be false
>
> You could use:
> =IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,LEFT(A1,LEN(A1)-1))
> though.
>
> Kevin B wrote:
> >
> > Insert a helper column to the right of the column containing your text string
> > and enter the following formula:
> >
> > =IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))
> >
> > The formula example starts in B1, and references the data in A1.
> >
> > If the last character in A1 is a number, use the current value, otherwise
> > extract the number of characters from the value that is the value of its
> > length minus 1
> >
> > --
> > Kevin Backmann
> >
> > "Funkey" wrote:
> >
> > > I have a lot of text data in a column. The text is different lengths.(see
> > > below). I want to remove the last letter. I cannot use text to columns
> > > because it left justifies(is there some way to right justify). I tries left
> > > and right functions but becuase the text is different lengths (some 5, 6,7,
> > > 10 characters etc.). this does not work. I looked at truncate but it is
> > > text not numbers to this does not work.
> > > Finally I thought of Find and Replace but because the letter may be in the
> > > body of the text as well as a suffix, this does not work. It cannot be done
> > > by hand accurately becasue I have thousands of items. Any Ideas????
> > >
> > >
> > > Thanks for taking time to look at this. I am trying to strip off the last
> > > one or
> > > two characters from the column B list. Since the text is different length
> > > it
> > > cannot be done with text to colums -this uses a left justify. I tried right
> > > and left functions
> > > but again with no luck. The find & replace won"t work because the same
> > > letter sometimes
> > > is in the number as well as at the end. There are too many to do it by hand.
> > > Any ideas?
> > >
> > >
> > > Original Corrected
> > >
> > > SYJ600002 SYJ600002
> > > SYJ600002 SYJ600002
> > > SYJ6014004 SYJ6014004
> > > M680972 M680972
> > > M580619T M580619 Remomve T
> > > SYJ600002 SYJ600002
> > > SYJ600002 SYJ600002
> > > SYJ6000001 SYJ6000001
> > > SYJ6000001 SYJ6000001
> > > SYJ6009004 SYJ6009004
> > > SYJ6009010 SYJ6009010
> > > 226540A 226540 Remove A
> > > SYJ6011003E SYJ6011003 Remove E
> > > 404881A 404881 Remove A
> > > SY600000071 SY600000071
> > > SYN50479B SYN50479 Remove B
> > > CSQ060059 CSQ060059
> > > SYJ6030014 SYJ6030014
> > > T4436605 T4436605
> > > T4436605 T4436605
> > > BO4K440674C BO4K440674 Remove C
> > > 405247A 405247 Remove A
> > > SYJ600002 SYJ600002
> > > SYJ6014004 SYJ6014004
> > > SYJ6014004 SYJ6014004
> > > SYJ6026001 SYJ6026001
> > > SYJ6000001 SYJ6000001
> > > SYJ6011003G SYJ6011003 Remove G
> > > 405464A 405464 Remove A
> > > SYJ6026002 SYJ6026002
> > > CSYN50003 CSYN50003
> > > CSYN50003 CSYN50003
> > >
> > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

Dave Peterson · 03-29-2006, 02:25 PM

Justification (affects formatting--not the value) won't matter--trailing spaces
will.

Funkey wrote:
>
> Dave
>
> This will work if the text is right justified and there are no spaces after
> the text.
> You guys are good!!!!
>
> Thanks a million
>
> "Dave Peterson" wrote:
>
> > =right() always returns text.
> > So =isnumber(right()) will always be false
> >
> > You could use:
> > =IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,LEFT(A1,LEN(A1)-1))
> > though.
> >
> > Kevin B wrote:
> > >
> > > Insert a helper column to the right of the column containing your text string
> > > and enter the following formula:
> > >
> > > =IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))
> > >
> > > The formula example starts in B1, and references the data in A1.
> > >
> > > If the last character in A1 is a number, use the current value, otherwise
> > > extract the number of characters from the value that is the value of its
> > > length minus 1
> > >
> > > --
> > > Kevin Backmann
> > >
> > > "Funkey" wrote:
> > >
> > > > I have a lot of text data in a column. The text is different lengths.(see
> > > > below). I want to remove the last letter. I cannot use text to columns
> > > > because it left justifies(is there some way to right justify). I tries left
> > > > and right functions but becuase the text is different lengths (some 5, 6,7,
> > > > 10 characters etc.). this does not work. I looked at truncate but it is
> > > > text not numbers to this does not work.
> > > > Finally I thought of Find and Replace but because the letter may be in the
> > > > body of the text as well as a suffix, this does not work. It cannot be done
> > > > by hand accurately becasue I have thousands of items. Any Ideas????
> > > >
> > > >
> > > > Thanks for taking time to look at this. I am trying to strip off the last
> > > > one or
> > > > two characters from the column B list. Since the text is different length
> > > > it
> > > > cannot be done with text to colums -this uses a left justify. I tried right
> > > > and left functions
> > > > but again with no luck. The find & replace won"t work because the same
> > > > letter sometimes
> > > > is in the number as well as at the end. There are too many to do it by hand.
> > > > Any ideas?
> > > >
> > > >
> > > > Original Corrected
> > > >
> > > > SYJ600002 SYJ600002
> > > > SYJ600002 SYJ600002
> > > > SYJ6014004 SYJ6014004
> > > > M680972 M680972
> > > > M580619T M580619 Remomve T
> > > > SYJ600002 SYJ600002
> > > > SYJ600002 SYJ600002
> > > > SYJ6000001 SYJ6000001
> > > > SYJ6000001 SYJ6000001
> > > > SYJ6009004 SYJ6009004
> > > > SYJ6009010 SYJ6009010
> > > > 226540A 226540 Remove A
> > > > SYJ6011003E SYJ6011003 Remove E
> > > > 404881A 404881 Remove A
> > > > SY600000071 SY600000071
> > > > SYN50479B SYN50479 Remove B
> > > > CSQ060059 CSQ060059
> > > > SYJ6030014 SYJ6030014
> > > > T4436605 T4436605
> > > > T4436605 T4436605
> > > > BO4K440674C BO4K440674 Remove C
> > > > 405247A 405247 Remove A
> > > > SYJ600002 SYJ600002
> > > > SYJ6014004 SYJ6014004
> > > > SYJ6014004 SYJ6014004
> > > > SYJ6026001 SYJ6026001
> > > > SYJ6000001 SYJ6000001
> > > > SYJ6011003G SYJ6011003 Remove G
> > > > 405464A 405464 Remove A
> > > > SYJ6026002 SYJ6026002
> > > > CSYN50003 CSYN50003
> > > > CSYN50003 CSYN50003
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson