On Tue, 24 Jan 2006 04:12:01 -0800, "Monk"
wrote:

Hello..

I did post this question yesterday and following a fantastic response I
thought I had it rapid up..
However the cool formula provided has a slight defect to it..

Ok.. The problem..., I have a vast list of cells with numbers with in them
(which is good) however the string of numbers sometimes commence with the
‘character and sometimes may end with a ‘character or both in some cases,
(which is bad).

Example A1: ‘1234435999 A2: 34566673341 A3: ’444532322711’

It appears completely random whether the cells contain any ‘character or is
completely free from the little blighters.
So I need a formula (rather then macro) to remove all the ‘’’’ characters in
one go (rather then using the longwinded Left / Right formula)

Yesterday I was given the following impressive formula…
B1
=LEFT(MID(P29,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},P2 9&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&P29))),SUMPRODUCT(--ISNUMBER(--MID(P29,ROW(INDIRECT("1:"&(LEN(P29)))),1))))

This appeared to work perfectly, however I have since noticed if the cell
ENDS with any consecutive digits (ie. 35999) the formula only appears to
generate one of the digits (359). Can anyone amend the formula or provide a
new one to get around my dilemma ..?

Many thanks,
Monk

The REGEX formula I posted yesterday will handle all that.
--ron