Solved

Excel list clean up

Posted on 2016-10-22
6
61 Views
Last Modified: 2016-10-23
The list is one column with many rows. Many of the rows contain word, or words and numbers.
I am seeking a script that I can amend (i.e include different words if required) to be able to remove all words, (including letter/number combinations like m2 - see list) in one run without changing the cell location of the number that is left.
example-list.xlsx
0
Comment
Question by:gregfthompson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41855727
You may try something like this....

Sub ReplaceWords()
Dim replWhat As String
Dim replWith As String
replWhat = Application.InputBox("What word/string you want to replace?", "String To Be Replaced!")
If replWhat = "False" Then
   MsgBox "You didn't input the string to be replaced", vbExclamation
   Exit Sub
End If
replWith = Application.InputBox("What is the word/string you want to replace with?", "String To Be Replaced With!")

Columns(1).Replace What:=replWhat, Replacement:=replWith, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
MsgBox "All the occurrences of " & replWhat & " were replaced with " & replWith & " successfully.", vbInformation, "Done!"
End Sub

Open in new window

0
 

Author Comment

by:gregfthompson
ID: 41855737
Thanks.

 amended it to this:
Sub ReplaceWords()
Dim replWhat As String
Dim replWith As String
replWhat = Application.InputBox("m2", "(approx)", "Square Mtr Approx", "sqm", "m2", "squareMeter", "(approx)", "m²", "m?", "approx.", "sq m", "squaremeter", "square metres", "Square Metres", "m")
If replWhat = "False" Then
   MsgBox "You didn't input the string to be replaced", vbExclamation
   Exit Sub
End If
replWith = Application.InputBox("What is the word/string you want to replace with?", "String To Be Replaced With!")

Columns(1).Replace What:=replWhat, Replacement:=replWith, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
MsgBox "All the occurrences of " & replWhat & " were replaced with " & replWith & " successfully.", vbInformation, "Done!"
End Sub
 
 But it would not run.
0
 

Author Comment

by:gregfthompson
ID: 41855738
It said Wrong number of arguments or invalid property assignment.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41855742
Try it like this.....
When prompted to input the words to be replaced, list all the words separated by a comma in the first input box and in the second input box, input the word which you want to replace the above mentioned words with. If you just want to remove those words, leave the second input box blank and click OK.
Sub ReplaceWords()
Dim lr As Long, i As Long
Dim rng As Range
Dim replWhat As String
Dim replWith As String
Dim str() As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & lr)
replWhat = Application.InputBox("What word/string you want to replace?", "String To Be Replaced!")
If replWhat = "False" Then
   MsgBox "You didn't input the string to be replaced", vbExclamation
   Exit Sub
End If
str() = Split(replWhat, ",")
replWith = Application.InputBox("What is the word/string you want to replace with?", "String To Be Replaced With!")

For i = 0 To UBound(str)
   If str(i) <> " " Then
      rng.Replace What:=str(i), Replacement:=replWith, LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
    End If
Next i
End Sub

Open in new window

0
 

Author Comment

by:gregfthompson
ID: 41855749
Thanks:I tried this, but it seems there are too many words for the input line.


Sub ReplaceWords()
Dim replWhat As String
Dim replWith As String
replWhat = Application.InputBox($ +,(approx),/,+,app,Approx,approx,approx,Approx,approx,approx m2,approx m2,approx s,Approx sqm,Approx Square metres,Approx square metres,Aprox m2,Apx,Auction,From,from: m2,in excess of sqmtrs,IncludesBAL m2,m,m,m x m,m?,m?,m?,m2,m2,m2,m2,m2,m²,m²,m2 &,m2 approx,m2 mm2,m²,m²,m²,m²,m² approx,Metres²,metres²,mm2,ms,mt2,mt2 approx,N/A,nil,over,Over sqm,sq,sq,sq m,sq m,sq m,sq m,sq m approx,sq metres,sq metres,sq metresw,Sqft,sqm,sqm,sqm,sqm,sqm,sqm,sqm approx,sqm approx,Sqms,sqms,sqr,sqr,sqrs m2,square,square,Square Metre,square Metre,Square Metres,Square Metres,square metres,Square Metres,square metres,Square Mtr,Square Mtr,Square Mtr approx,Square Mtr Approx,square units,squareMeter,squareMeter,squares m2,Townhouse,Unit,Unit sqm,Unknown,Varied,Various Sizes,VIC Sqms)
If replWhat = "False" Then
   MsgBox "You didn't input the string to be replaced", vbExclamation
   Exit Sub
End If
replWith = Application.InputBox("What is the word/string you want to replace with?", "String To Be Replaced With!")

Columns(1).Replace What:=replWhat, Replacement:=replWith, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
MsgBox "All the occurrences of " & replWhat & " were replaced with " & replWith & " successfully.", vbInformation, "Done!"
End Sub
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41855758
It is better to have a separate sheet with a list of words you want to remove from Sheet2. See the Sheet Word List in the attached. Just mention all the words in column A and click the button on Sheet2 to remove those words from column A on Sheet2.
Example-list.xlsm
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question