truncating text strings of different lengths

G

Guest

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
 
G

Guest

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
 
D

Dave Peterson

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)))
 
G

Guest

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
 
D

Dave Peterson

=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.
 
G

Guest

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
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top