Hi Ron,

I did try this but unable to complete the download ("keeps saying Compressed
(zipped) folder is invalid or corrupt").. This may have something to do with
our security protection preventing the download (not sure).

Also if I did manage to download and use the extra formulas, would everyone
who wishes to use the spreadsheet have to do the same..?

Thanks,
Monk





"Ron Rosenfeld" wrote:

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