Cool..,
This new formula is perfect..
Many thanks to all (Again)

Monk :0)




"bob777" wrote:


Assuming your unwanted character is an €œa€� and that your data is in
column A starting in row 1, type this formula into cell B1 and copy it
down. This deals with all 4 possibilities a 77999a a77999
77999a 77999


IF(LEFT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",IF(RI GHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",MID(a1,2, LEN(a1)-2),MID(a1,2,LEN(a1)-1)),IF(RIGHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a", LEFT(a1,LEN(a1)-1),a1))


I have posted this so that you can see how to deal with numbers and
text strings of varying length.

Bobf


--
bob777
------------------------------------------------------------------------
bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504
View this thread: http://www.excelforum.com/showthread...hreadid=504430