software

Column quot;Aquot; 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

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.

PeteHi Kim,

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

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

Set oRegExp = CreateObject(quot;VBScript.RegExpquot;)

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = quot;\Dquot;

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

For example:

A1: Service 190,000 Klm
B1: = DigitsOnly(quot;A1quot;) ==gt; 190, 000 (with suitable formatting)

---
Regards,
Normanquot;Kimquot; gt; wrote in message
...
gt; Column quot;Aquot; has a few variations eg:
gt;
gt; 90,000 K
gt; 190,000 Klm Ser
gt; Service 190,000 Klm
gt;
gt; I want to remove all characters from each cell to leave in the adjacent
gt; cell
gt; the following
gt;
gt; 90,000
gt; 190,000
gt; 190,000
gt;
gt; I've done find and replace and recorded a macro but I need to update the
gt; sheet each day and want it to be more automatic. The sheet has around
gt; 15000
gt; rows so it takes a while to remove all variations manually, each time it's
gt; updated.
gt;
gt; I'd appreciate any help
gt;
gt; Thanks
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, CABGx3quot;Kimquot; gt; wrote in message
...
gt; Column quot;Aquot; has a few variations eg:
gt;
gt; 90,000 K
gt; 190,000 Klm Ser
gt; Service 190,000 Klm
gt;
gt; I want to remove all characters from each cell to leave in the adjacent
cell
gt; the following
gt;
gt; 90,000
gt; 190,000
gt; 190,000
gt;
gt; I've done find and replace and recorded a macro but I need to update the
gt; sheet each day and want it to be more automatic. The sheet has around
15000
gt; rows so it takes a while to remove all variations manually, each time it's
gt; updated.
gt;
gt; I'd appreciate any help
gt;
gt; Thanks
I created the User Definable Function. Thank you for that. But I have in
cell A1
quot;Service 190,000 Klmquot; and in cell B1 I have quot;=digitsonly(quot;A1quot;)quot; but the
answer comes back as quot;1quot;.

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

quot;Norman Jonesquot; wrote:

gt; Hi Kim,
gt;
gt; In a helper column, try using the following User Defined Function:
gt;
gt; '=============gt;gt;
gt; Public Function DigitsOnly(sStr As String) As Variant
gt; Dim oRegExp As Object
gt;
gt; Set oRegExp = CreateObject(quot;VBScript.RegExpquot;)
gt;
gt; With oRegExp
gt; .IgnoreCase = True
gt; .Global = True
gt; oRegExp.Pattern = quot;\Dquot;
gt;
gt; DigitsOnly = oRegExp.Replace(sStr, vbNullString)
gt; End With
gt; End Function
gt; 'lt;lt;=============
gt;
gt; For example:
gt;
gt; A1: Service 190,000 Klm
gt; B1: = DigitsOnly(quot;A1quot;) ==gt; 190, 000 (with suitable formatting)
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt; quot;Kimquot; gt; wrote in message
gt; ...
gt; gt; Column quot;Aquot; has a few variations eg:
gt; gt;
gt; gt; 90,000 K
gt; gt; 190,000 Klm Ser
gt; gt; Service 190,000 Klm
gt; gt;
gt; gt; I want to remove all characters from each cell to leave in the adjacent
gt; gt; cell
gt; gt; the following
gt; gt;
gt; gt; 90,000
gt; gt; 190,000
gt; gt; 190,000
gt; gt;
gt; gt; I've done find and replace and recorded a macro but I need to update the
gt; gt; sheet each day and want it to be more automatic. The sheet has around
gt; gt; 15000
gt; gt; rows so it takes a while to remove all variations manually, each time it's
gt; gt; updated.
gt; gt;
gt; gt; I'd appreciate any help
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

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 quot;NOSPAM and replace quot;DOTquot; with a period [full stop])

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

gt; DigitsOnly = oRegExp.Replace(sStr, vbNullString)

with

DigitsOnly = CLng(oRegExp.Replace(sStr, vbNullString))---
Regards,
Normanquot;Kimquot; gt; wrote in message
...
gt;I created the User Definable Function. Thank you for that. But I have in
gt; cell A1
gt; quot;Service 190,000 Klmquot; and in cell B1 I have quot;=digitsonly(quot;A1quot;)quot; but the
gt; answer comes back as quot;1quot;.
gt;
gt; What am I doing wrong?
gt;
gt; P.S. I'm new to creating User Definable Functions but I inserted a module
gt; into this sheet after hitting ALT F11. So I think that's not the problem.
gt;
gt; Thanks
Hi Kim,

The fault was mine!

The formula should have read:

= DigitsOnly(A1)

without the quotation marks.---
Regards,
Norman
The user definable function worked.

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

Thanks to all

quot;Norman Jonesquot; wrote:

gt; Hi Kim,
gt;
gt; I am unable to reproduce your result; I continue to get the expected 190000.
gt;
gt; If you wish, send me a sample of your problematic result(s):
gt;
gt; norman_jones@NOSPAMbtconnectDOTcom
gt;
gt; (Delete quot;NOSPAM and replace quot;DOTquot; with a period [full stop])
gt;
gt; Incidentally, and not germane to your immediate problem, replace:
gt;
gt; gt; DigitsOnly = oRegExp.Replace(sStr, vbNullString)
gt;
gt; with
gt;
gt; DigitsOnly = CLng(oRegExp.Replace(sStr, vbNullString))
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt; quot;Kimquot; gt; wrote in message
gt; ...
gt; gt;I created the User Definable Function. Thank you for that. But I have in
gt; gt; cell A1
gt; gt; quot;Service 190,000 Klmquot; and in cell B1 I have quot;=digitsonly(quot;A1quot;)quot; but the
gt; gt; answer comes back as quot;1quot;.
gt; gt;
gt; gt; What am I doing wrong?
gt; gt;
gt; gt; P.S. I'm new to creating User Definable Functions but I inserted a module
gt; gt; into this sheet after hitting ALT F11. So I think that's not the problem.
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;


I've tried this also, but I get an error quot;#Name!quot; 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.--
pdgood
------------------------------------------------------------------------
pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=544501