Re: Removing characters from a cell (keeping only numbers) 2
- From: Ron Rosenfeld <ronrosenfeld@xxxxxxxxxx>
- Date: Tue, 24 Jan 2006 08:36:25 -0500
On Tue, 24 Jan 2006 04:12:01 -0800, "Monk" <Monk@xxxxxxxxxxxxxxxxxxxxxxxxx>
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},P29&"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
.
- Prev by Date: Re: excel trying to sum a column of calculated values
- Next by Date: Re: Adding colour to result in a cell?
- Previous by thread: Re: excel trying to sum a column of calculated values
- Next by thread: Re: Removing characters from a cell (keeping only numbers) 2
- Index(es):
Relevant Pages
|
