Removing special characters

The below code allows you to display only the digits contained in a referenced dell:

• ALT+F11...to open the VBA editor
• Insert.Module

In the new module:
• Copy the below code and paste it into that module:

Please Login or Register  to view this content.

With
A1: +27(0)114831798
and
B1: =PullDigits(A1)

B1 returns: 270114831798

Is that something you can work with?

Try this array formula

=NPV(-0.9,,IFERROR((MID(A1,256-COLUMN(A:IV),1)%),""))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B
1	+27(0)114831798	270114831798
2	+27(0)11a5315080	270115315080
3	+27(0)115606599	270115606599

"Not working" is a bit vague.
Does it crash your computer? Return the wrong value? Doesn't compile?
An example would be nice, too.

I attached a file with a working example:
sathishkm_demo.xlsm

Note: Because the returned value is text, the function can accommodate a source value like this (Which has more than 15 significant digits):
+ 27(0)114831798 x3355

For that value, the PullDigits function returns: 2701148317983355

Does that help?

Hi..

Here's another possible way..

Although it does use a loop.. the maximum number of loops is defined by how many values you want to check for).

In this case.. I have added left and right brackets and the + symbol as well as a-z. Add more if you need.

So.. although looping is to be avoided.. looping just 29 times to apply the Replace maybe not be that bad especially if you're doing 500K rows.

Please Login or Register  to view this content.

Edit: I just tested it on 200 000 rows.. it took about 15 seconds to complete.