Removing characters from a cell (keeping only numbers) 2

[Monk]

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

 

[Ron Rosenfeld]

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

 

[Don Guillett]

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

 

[Monk]

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

 

[bob777]

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(RIGHT(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

 

[Ron Rosenfeld]

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).

That's wierd. I've never had a problem downloading.

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..?

There is a selectable option to include morefunc "in the workbook" so that
would not be necessary when you distribute it.

However, you could also use a fairly simple UDF to extract the digits. And
this, too, would be distributed with the workbook. (But there are a bunch of
other useful functions in morefunc).

1. <alt><F11> opens the VB Editor

2. Ensure your project is selected in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

3. Use the formula by entering =Digits(cell_ref) or =Digits("string" into some
cell.

4. The formula returns the digits as a string (text). If you want them
returned as a number, change the first line to

Function Digits(str As String) As Double

However, this may give unexpected results depending on the cell formatting
(i.e. with large values and General formatting, Excel may give you Scientific
notation).

=========================
Option Explicit

Function Digits(str As String) As String
Dim i As Long
Dim t As String

For i = 1 To Len(str)
If Mid(str, i, 1) Like "[0-9]" Then
t = t & Mid(str, i, 1)
End If
Next i
Digits = t
End Function
===========================

Thanks,
Monk

--ron

 

[Monk]

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

Monk :0)