Tech Support Forum banner
Status
Not open for further replies.

[SOLVED] Selecting cells with only numbers in MS word and deleting them

1K views 14 replies 2 participants last post by  macropod 
#1 · (Edited)
Hi,

I am new here. dont have much ideas about macros. I wanted to know if there is a macro that will allow you to search the whole word document (document will only contain tables),with multiple rows and columns, find all cells with numbers and number separators only (like "," or ".") and delete all these numbers. it should however leave all the cells that have alphabets (abcd) along with text. Like for example if a cell has "janury 24, 2014" or "there were 5 items sold last week", it will leave it as it is but if has a number like "2,123,456" or "223.15", it will be deleted.

I am sorry if i cant explain it any better. I know there is a "find and select" "constants" option in MS excel. i am looking for something similar in word.

Thank you
 
#2 ·
Re: Selecting cells with only numbers in MS word and deleting them

You could use a wildcard Find/Replace, where:
Find = [0-9]@[.\,][0-9.\,]{1,}
Replace = \1
No vba required. Note that this is liable to delete numbers that precede a period or comma, along with that period or comma. If that's an issue, vba will be required.
 
#3 ·
Re: Selecting cells with only numbers in MS word and deleting them

You could use a wildcard Find/Replace, where:
Find = [0-9]@[.\,][0-9.\,]{1,}
Replace = \1
No vba required. Note that this is liable to delete numbers that precede a period or comma, along with that period or comma. If that's an issue, vba will be required.
Hi,

Thanks for the quick reply. I tried the replace all command but it gave me no hits. I am attaching a screen shot of what i am saying.

from the picture attached, i want the cells in orange to remain as it is because it only has text. i also want cells in blue to remain the same because it has numbers and text. the cells (and others similar to it) marked in red are the ones i want emptied. Now here is another slight problem. as you can see, we get data in which the number separator is "." instead of ",". so the word (and excel) reads it as 4 point 312 point 220 (two decimals). we need to tackle that as well. but for starters if i can get the macro for the normal number removal, that would be great.
 

Attachments

#4 ·
Re: Selecting cells with only numbers in MS word and deleting them

As indicated in my previous post, this is a wildcard Find/Replace. Did you check the 'use wildcards' option? Also, since it appears you may be using a regional setting that uses commas as a decimal separator, you might need to change the Find expression to:
Find = [0-9]@[.\,][0-9.\,]{1;}
 
#5 ·
Re: Selecting cells with only numbers in MS word and deleting them

As indicated in my previous post, this is a wildcard Find/Replace. Did you check the 'use wildcards' option? Also, since it appears you may be using a regional setting that uses commas as a decimal separator, you might need to change the Find expression to:
Find = [0-9]@[.\,][0-9.\,]{1;}
Oooops.. i forgot to use the "use wildcard" option. now i am getting an error that "the Replace With text contains a group number which is out of range." i am a total noob at this so please dont mind...
 
#7 ·
Re: Selecting cells with only numbers in MS word and deleting them

Oops from me too - the Replace should be left empty.
Ok. the first replace command that you posted works now but it is not replacing any numbers that do not have separators e.g. if it is just 345, it will still be there but if it is 345.678, it will be removed. As for the second replace all, i am getting this error " The Find What text contains a pattern match expression which is not valid"

And by the way, really appreciate you taking your time out to help me. thanks :)
 
#10 ·
Re: Selecting cells with only numbers in MS word and deleting them

So do you want to keep the numbers within the brackets, or delete the brackets as well?

PS: Please don't quote the entire posts replied to. If there's something you need to cite, quote just that part.
 
#13 ·
Re: Selecting cells with only numbers in MS word and deleting them

Changing the requirements like that means an entirely different approach is needed. Try the following macro:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim Tbl As Table, TblCell As Cell, Rng As Range
For Each Tbl In ActiveDocument.Tables
  For Each TblCell In Tbl.Range.Cells
    Set Rng = TblCell.Range
    With Rng
      .End = .End - 1
      If IsNumeric(.Text) Then
        .Text = vbNullString
      ElseIf InStr(.Text, Chr(146)) > 0 Then
        If IsNumeric(Replace(Replace(Replace(Replace(.Text, Chr(146), ""), ",", ""), ".", ""), "%", "")) Then _
          .Text = vbNullString
      ElseIf InStr(.Text, ",") > 0 Then
        If IsNumeric(Replace(Replace(Replace(Replace(.Text, Chr(146), ""), ",", ""), ".", ""), "%", "")) Then _
          .Text = vbNullString
      ElseIf InStr(.Text, ".") > 0 Then
        If IsNumeric(Replace(Replace(Replace(Replace(.Text, Chr(146), ""), ",", ""), ".", ""), "%", "")) Then _
          .Text = vbNullString
      ElseIf InStr(.Text, "%") > 0 Then
        If IsNumeric(Replace(Replace(Replace(Replace(.Text, Chr(146), ""), ",", ""), ".", ""), "%", "")) Then _
          .Text = vbNullString
      End If
    End With
  Next
Next
Application.ScreenUpdating = True
End Sub
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top