| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
truncating text strings of different lengths
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
=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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
truncating text strings of different lengths
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert text strings to a code or number | Excel Worksheet Functions | |||
| Truncating text before a hyphen | Excel Worksheet Functions | |||
| Finding Duplicate text strings with a single column | Excel Worksheet Functions | |||
| space between text strings with concatenate | Excel Discussion (Misc queries) | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Hybrid Mode
