Re: Removing characters from a cell (keeping only numbers) 2
- From: "Don Guillett" <dguillett1@xxxxxxxxxxxxx>
- Date: Tue, 24 Jan 2006 08:02:01 -0600
Put this code in a REGULAR module and then
=striptxt(a1)
Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If Asc(b) > 47 And Asc(b) < 58 Then StripTxt = StripTxt + b
Next i
End Function
--
Don Guillett
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"Monk" <Monk@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A80F0730-0CC5-4FFE-8168-9FFD34816E1E@xxxxxxxxxxxxxxxx
> 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
>
.
- Prev by Date: Re: timer macro
- Next by Date: Re: If, Then statements with a formula
- Previous by thread: Re: Removing characters from a cell (keeping only numbers) 2
- Next by thread: Re: #VALUE error with XIRR
- Index(es):
Relevant Pages
|
