gregfthompson
asked on
Excel list clean up
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
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
ASKER
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.
amended it to this:
Sub ReplaceWords()
Dim replWhat As String
Dim replWith As String
replWhat = Application.InputBox("m2",
If replWhat = "False" Then
MsgBox "You didn't input the string to be replaced", vbExclamation
Exit Sub
End If
replWith = Application.InputBox("What
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.
ASKER
It said Wrong number of arguments or invalid property assignment.
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.
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
ASKER
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,app rox,Approx ,approx,ap prox 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,mt 2 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,s qm,sqm,sqm ,sqm approx,sqm approx,Sqms,sqms,sqr,sqr,s qrs 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,squareMe ter,square s 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
Sub ReplaceWords()
Dim replWhat As String
Dim replWith As String
replWhat = Application.InputBox($ +,(approx),/,+,app,Approx,
If replWhat = "False" Then
MsgBox "You didn't input the string to be replaced", vbExclamation
Exit Sub
End If
replWith = Application.InputBox("What
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window