Removing text characters

S

Scott

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
 
G

Gary's Student

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
 
A

Arvi Laanemets

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).
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top