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

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

=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

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

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