Remove Characters from a cell

[Guest]

Column "A" has a few variations eg:

90,000 K
190,000 Klm Ser
Service 190,000 Klm

I want to remove all characters from each cell to leave in the adjacent cell
the following

90,000
190,000
190,000

I've done find and replace and recorded a macro but I need to update the
sheet each day and want it to be more automatic. The sheet has around 15000
rows so it takes a while to remove all variations manually, each time it's
updated.

I'd appreciate any help

Thanks

 

[Pete_UK]

You could set up a User-defined function into which is passed the
string from A1 and this is examined character by character with only
the digits 0-9 and the comma allowed to remain in a replacement string
which is returned (for example) to B1. This can then be copied down
column B.

Hope this helps.

Pete

 

[Norman Jones]

Hi Kim,

In a helper column, try using the following User Defined Function:

'=============>>
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function
'<<=============

For example:

A1: Service 190,000 Klm
B1: = DigitsOnly("A1") ==> 190, 000 (with suitable formatting)

 

[CLR]

ASAP Utilities, a free Add-in available from www.asap-utilities.com has a
feature that will remove all alpha characters from the selection...........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

I created the User Definable Function. Thank you for that. But I have in
cell A1
"Service 190,000 Klm" and in cell B1 I have "=digitsonly("A1")" but the
answer comes back as "1".

What am I doing wrong?

P.S. I'm new to creating User Definable Functions but I inserted a module
into this sheet after hitting ALT F11. So I think that's not the problem.

Thanks

 

[Norman Jones]

Hi Kim,

I am unable to reproduce your result; I continue to get the expected 190000.

If you wish, send me a sample of your problematic result(s):

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM and replace "DOT" with a period [full stop])

Incidentally, and not germane to your immediate problem, replace:

DigitsOnly = oRegExp.Replace(sStr, vbNullString)

with

DigitsOnly = CLng(oRegExp.Replace(sStr, vbNullString))

 

[Norman Jones]

Hi Kim,

The fault was mine!

The formula should have read:

= DigitsOnly(A1)

without the quotation marks.

 

[Guest]

The user definable function worked.

I also download asap utilities, which I think will prove to be a valuable
tool.

Thanks to all

Hi Kim,

I am unable to reproduce your result; I continue to get the expected 190000.

If you wish, send me a sample of your problematic result(s):

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM and replace "DOT" with a period [full stop])

Incidentally, and not germane to your immediate problem, replace:

DigitsOnly = oRegExp.Replace(sStr, vbNullString)

with

DigitsOnly = CLng(oRegExp.Replace(sStr, vbNullString))


---
Regards,
Norman

I created the User Definable Function. Thank you for that. But I have in
cell A1
"Service 190,000 Klm" and in cell B1 I have "=digitsonly("A1")" but the
answer comes back as "1".

What am I doing wrong?

P.S. I'm new to creating User Definable Functions but I inserted a module
into this sheet after hitting ALT F11. So I think that's not the problem.

Thanks

 

[pdgood]

I've tried this also, but I get an error "#Name!" in B1. I take this to
mean that I have done something wrong in creating the function. I
created a module in F11 and pasted in the code and made the corrections
listed. (Does it need to be named? Does it need to be run? Am I missing
a step?)
Then I set up the work sheet to duplicate Kim's entry in A1 and the new
function in B1.
Puzzled.