Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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) --- Regards, Norman "Kim" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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 "Kim" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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" wrote: 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) --- Regards, Norman "Kim" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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 "Kim" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
Hi Kim,
The fault was mine! The formula should have read: = DigitsOnly(A1) without the quotation marks. --- Regards, Norman |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
The user definable function worked.
I also download asap utilities, which I think will prove to be a valuable tool. Thanks to all "Norman Jones" wrote: 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 "Kim" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Characters from a cell
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. -- pdgood ------------------------------------------------------------------------ pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623 View this thread: http://www.excelforum.com/showthread...hreadid=544501 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
How to set number of characters within a cell in Excel 2003? | Excel Worksheet Functions | |||
Display text 1024 characters in a cell | Excel Worksheet Functions | |||
How can I increase the number of printable characters in a cell? | Excel Discussion (Misc queries) | |||
remove last three characters of cell | Excel Discussion (Misc queries) |