Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing text characters
Hi,
I have a column of text reference codes, which I need to adapt into a more appropriate format (values). NOW DESIRED 709A 709 495R 495 1B 1 1075T 1075 111115D 111115 Effectively, I need to remove the text character at the end of the code, which will then allow me to convert the former text code into a value. If there is a function I can use this would help me greatly. Kind regards, Scott |
#2
|
|||
|
|||
Assuming that you have a single character at the end of a sequence of digits,
use: =LEFT(A1,LEN(A1)-1) to strip the character from cell A1. Copy as required -- Gary's Student "Scott" wrote: Hi, I have a column of text reference codes, which I need to adapt into a more appropriate format (values). NOW DESIRED 709A 709 495R 495 1B 1 1075T 1075 111115D 111115 Effectively, I need to remove the text character at the end of the code, which will then allow me to convert the former text code into a value. If there is a function I can use this would help me greatly. Kind regards, Scott |
#3
|
|||
|
|||
=SUBSTITUTE(A1,RIGHT(A1,1),"")*1
Regards, Alan. "Scott" wrote in message ... Hi, I have a column of text reference codes, which I need to adapt into a more appropriate format (values). NOW DESIRED 709A 709 495R 495 1B 1 1075T 1075 111115D 111115 Effectively, I need to remove the text character at the end of the code, which will then allow me to convert the former text code into a value. If there is a function I can use this would help me greatly. Kind regards, Scott |
#4
|
|||
|
|||
Hi
When the max number of characters in code is 6 (as in your example), then for code in cell A2: =(IF(ISNUMBER(MID(A2,1,1)*1),MID(A2,1,1),"") & IF(ISNUMBER(MID(A2,2,1)*1),MID(A2,2,1),"") & IF(ISNUMBER(MID(A2,3,1)*1),MID(A2,3,1),"") & IF(ISNUMBER(MID(A2,4,1)*1),MID(A2,4,1),"") & IF(ISNUMBER(MID(A2,5,1)*1),MID(A2,5,1),"") & IF(ISNUMBER(MID(A2,6,1)*1),MID(A2,6,1),""))*1 , which returns a number, or: =IF(ISNUMBER(MID(A2,1,1)*1),MID(A2,1,1),"") & IF(ISNUMBER(MID(A2,2,1)*1),MID(A2,2,1),"") & IF(ISNUMBER(MID(A2,3,1)*1),MID(A2,3,1),"") & IF(ISNUMBER(MID(A2,4,1)*1),MID(A2,4,1),"") & IF(ISNUMBER(MID(A2,5,1)*1),MID(A2,5,1),"") & IF(ISNUMBER(MID(A2,6,1)*1),MID(A2,6,1),"") , which returns a numeric string. When the max number of chareacters is 6, then add an IF(...) for every character in max length string - for formula length limit look in worksheet specifications in Help (but when the number of such formulas increases, the perfomance will drop heavily). -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Scott" wrote in message ... Hi, I have a column of text reference codes, which I need to adapt into a more appropriate format (values). NOW DESIRED 709A 709 495R 495 1B 1 1075T 1075 111115D 111115 Effectively, I need to remove the text character at the end of the code, which will then allow me to convert the former text code into a value. If there is a function I can use this would help me greatly. Kind regards, Scott |
#5
|
|||
|
|||
On Thu, 11 Aug 2005 03:43:03 -0700, "Scott"
wrote: Hi, I have a column of text reference codes, which I need to adapt into a more appropriate format (values). NOW DESIRED 709A 709 495R 495 1B 1 1075T 1075 111115D 111115 Effectively, I need to remove the text character at the end of the code, which will then allow me to convert the former text code into a value. If there is a function I can use this would help me greatly. Kind regards, Scott If all of your codes are as you posted, numbers with a single letter at the end, then to strip off the letter: =LEFT(A1,LEN(A1)-1) To also convert to a number: =--LEFT(A1,LEN(A1)-1) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Save As... text with special characters | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
how do I highlite text within a cell (specific characters) | Excel Discussion (Misc queries) | |||
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 |