How do you get rid of one character?

[HBF]

In a cell that has varying number of characters, how do you get rid of the number or letter on the right.

For instance: 10d needs to read 10, 109d needs to read 109, 5d needs to read 5.

Thanks in advance.

[VBA Noob]

Hi,

For a value in A1

If the numbers will always be the final characters in the cell contents, try
this:
B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)*1


Note: that formula returns an error if there are no numbers in the cell


This formula finds numbers anywhere in the cell (returns zero if none):
B1:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789*")),ROW($1:$10000))))

Note: in case text wrap impacts the display, there are no spaces in those
formulas.

VBA Noob

[BenjieLop]

In a cell that has varying number of characters, how do you get rid of the number or letter on the right.

For instance: 10d needs to read 10, 109d needs to read 109, 5d needs to read 5.

Thanks in advance.

Try this ...

=left(A1,len(A1)-1)

[xcelentform]

ASAP utilities has a comand that will do that for you in their Text submenue called "delete all text characters in selection"