Jump to content
LCVG

Help with Excel, Deleteing via parameters?


Gary P
 Share

Recommended Posts

Alright guys,

Need a little help here. I've got an Excel document that has a column containing both numbers and text (in the same cell) ie. 02155 POLICE. Not all of these are of the same length though, some may have 8 digits before the text starts, some may only have 4 or 5 etc... Is there any way to have Excel check each cell in a specific column and delete the text and just leave the numbers? I don't even know what you would call that, so I don't know what to Google. I thought maybe the 'Trim' command, but that doesn't seem to be what I'm after. If anybody has any thoughts they would be greatly appreciated.

 

Thanks,

Gary

Link to comment
Share on other sites

This is taken from here, I just tried it out and it seems to work.

 

  • Press Alt & F11 to go to the VBE
  • Insert->Module
  • Paste in:
    Sub KillNonNum()
       Dim C As Range, RegEx, i As Long
       Set RegEx = CreateObject("vbscript.regexp")
       RegEx.Global = True
       RegEx.Pattern = "[^\d\s]+"
       For Each C In Selection
           C.Value = RegEx.Replace(C.Value, "")
       Next
       Set RegEx = Nothing
    End Sub


  • Press Alt & F11 to return to Excel
  • Select your range of interest
  • Tools->Macro->Macros and doubleclick KillNonNum

 

You can also use the code

Function KillNonNum(AnyVal As String)
   Dim RegEx
   Set RegEx = CreateObject("vbscript.regexp")
   RegEx.Global = True
   RegEx.Pattern = "[^\d\s]+"
   KillNonNum = RegEx.Replace(AnyVal, "")
   Set RegEx = Nothing
End Function

instead, and use it as a function by placing for example

=KillNonNum(A1)

in another cell.

Link to comment
Share on other sites

I need just a wee bit more help. I stumbled around the MrExcel forums a bit but didn't find what I was after. I have some account numbers that have a dash in between digits (22-00300 POLICE). I'd like to be able to keep those as well, but with the above code they're being deleted as text. Any thoughts on that?

 

EDIT: Nevermind, I decided to use an add-in called ASAP Utilities that has an advanced character removal feature. Part of me hates doing that, as that's the easy way out and feels like surrender:)

 

Thanks,

Gary

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...