Oct 31 2018 09:26 AM
Dear friends,
I am trying to set up an instruction in Visual Basic to replace error in a text.
Selection.Replace What:="taic", Replacement:="taichi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
However I would like to replace with the same instruction several mistakes like "taic" OR "taihi" OR "tachi" OR etc.
Could someone tell me how to write properly all within this instruction?
Thank you, Juan
Oct 31 2018 10:36 AM - edited Oct 31 2018 10:39 AM
SolutionHi Juan,
I suggest this code:
Sub ReplaceTypos()
Dim typos As Variant
typos = Array("taic", "taihi", "tachi")
For Each typo In typos
Selection.Replace What:=typo, Replacement:="taichi", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
SearchFormat:=False, ReplaceFormat:=False
Next typo
End Sub
This code creates a list of typos and stores it in an array named (typos).
Then it will loop through each typo in typos array and apply the replacement on each one of them.
If you have other typos just expand the typos array list in the code as the syntax below.
typos = Array("typo 1", "typo 2", ...)
Hope that helps
Nov 01 2018 01:26 AM
Nov 03 2018 02:56 AM
hello again
i have tried to do two one after the other but it does not work... can you help me again?
Dim types As String
typos = Array("shark", "sharv", "sartv", "satv")
For Each typo In typos
Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim types As String
typos = Array("marsh", "mars", "marchan", "marshal", "marshall", "mashan", "mershan")
For Each typo In typos
Selection.Replace What:=typo, Replacement:="marisan", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Nov 03 2018 04:27 AM - edited Nov 03 2018 04:27 AM
Hi Juan,
You should declare the second group of typos in a different name, and both of them must be declared as Variant, not as String.
I've been declared it as String in the previous reply but I quickly updated it.
Sub ReplaceTypos2()
Dim typos1 As Variant
typos1 = Array("shark", "sharv", "sartv", "satv")
For Each typo In typos1
Selection.Replace What:=typo, Replacement:="sarv", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next typo
Dim typos2 As Variant
typos2 = Array("marsh", "mars", "marchan", "marshal", "marshall", "mashan", "mershan")
For Each typo In typos2
Selection.Replace What:=typo, Replacement:="marisan", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next typo
End Sub
Hope that helps
Nov 03 2018 05:08 AM
i just had to add "next typo 1, 2, etc" and it worked.
Thank you very much for your quick and useful help.
Have a nice weekend!
Nov 04 2018 08:52 AM
Dear Haytam,
Unfortunately what I have tried to develop myself did not work.
It is only recognizes the first word. Can you help?
Dim typos1 As Variant
typos = Array("pipí", "bibi", "pi pi")
For Each typo1 In typos
Selection.Replace What:=typo, Replacement:="pipi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo1
Dim typos2 As Variant
typos = Array("shark", "sharv", "sartv", "satv")
For Each typo2 In typos
Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim typos3 As Variant
typos = Array("marsan", "marchan", "marshal", "marshall", "mashan", "mershan")
For Each typo3 In typos
Selection.Replace What:=typo, Replacement:="marshan", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo3
Dim typos4 As Variant
typos = Array("findu", "facebook")
For Each typo4 In typos
Selection.Replace What:=typo, Replacement:="fidu", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo4
Nov 04 2018 09:56 AM
Hi Juan,
This is maybe a bit confusing at first glance, but it will be easy if you learned the basics of VBA.
This a delaration statement:
Dim typos1 As Variant
And this an assignment statement:
typos1 = Array("pip?", "bibi", "pi pi")
If you declare a variable such as (typos1), you should assign something to this variable!
And this what you didn't do!
You declared typos1, typos2, typos3, and typos4, but you didn't assign anything to these variables!
The assignment statement in each part is assigned to the same undeclared variable (typos).
After you declared typos1 (Dim typos1 as Variant), you should follow it by:
typos1 = Array("pipí", "bibi", "pi pi")
and so on...
With regards to the variable after the For Each, What:= and Next, please note that I didn't change it in each block, it's just typo as the previous reply.
The reason is that each For...Each block is independent of the other, so there is no need to change the name of this variable because it's defined only at the block level.
Also, you need to put the variable that you've declared after (In) in the loop statement
This your code before adjustment:
Sub ReplaceTypos()
Dim typos1 As Variant
typos = Array("pip?", "bibi", "pi pi")
For Each typo1 In typos
Selection.Replace What:=typo, Replacement:="pipi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo1
Dim typos2 As Variant
typos = Array("shark", "sharv", "sartv", "satv")
For Each typo2 In typos
Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim typos3 As Variant
typos = Array("marsan", "marchan", "marshal", "marshall", "mashan", "mershan")
For Each typo3 In typos
Selection.Replace What:=typo, Replacement:="marshan", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo3
Dim typos4 As Variant
typos = Array("findu", "facebook")
For Each typo4 In typos
Selection.Replace What:=typo, Replacement:="fidu", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo4
End Sub
This is after the adjustment:
---------------------------------------------------------------------------------------------------
Sub ReplaceTypos()
Dim typos1 As Variant
typos1 = Array("pip?", "bibi", "pi pi")
For Each typo In typos1
Selection.Replace What:=typo, Replacement:="pipi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim typos2 As Variant
typos2 = Array("shark", "sharv", "sartv", "satv")
For Each typo In typos2
Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim typos3 As Variant
typos3 = Array("marsan", "marchan", "marshal", "marshall", "mashan", "mershan")
For Each typo In typos3
Selection.Replace What:=typo, Replacement:="marshan", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim typos4 As Variant
typos4 = Array("findu", "facebook")
For Each typo In typos4
Selection.Replace What:=typo, Replacement:="fidu", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
End Sub
---------------------------------------------------------------------------------------------------
Please compare them, and note the difference.
I hope that makes sense
Nov 05 2018 02:04 AM
Dear Haytam,
It worked perfectly. Thank you very much for your help.
I would love to have the time to learn it properly. All I can only do is to record what I usually repeat and try to understand the commands on Vbasic.
I hope to have the time one day to learn it properly.
Anyway your enormous help . Have a nice week!
Nov 08 2018 04:51 AM
Hello again Haytam,
I have recorded some new commands within the macro where you have already helped me.
Enclose please find it:
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
The problem is that the original text:
10:40.XX.bbb.ccc
11:25.ddd.eee.fff
11:33.ggg.hhh.iii
11:42.jjj.kkk.lll
12:10.ppp.qqq
after running the macro, sometimes changes the first text into a time form and others not.
For instance:
10:40
11:25:00:AM
11:42
12:10:00:PM
And i would like to keep the text form.
Can you help me again?
Nov 08 2018 09:19 PM - edited Nov 08 2018 09:21 PM
Hi Juan,
I've tested this code, but ended up with this result:
Please note that all of the times are in Time format, even if the AM/PM isn't displayed, where it's visible in the formula bar!
I think the reason why you got some cells formatted in AM/PM is that these cells have a preset custom format to be formatted that way!
To keep all the results in Text format, where all cells are aligned to the left by default, just change the second argument for each Array function from 1 to 2 as follows:
Before:
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
After:
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2)), TrailingMinusNumbers:=True
Or you can change the format option to Text during recording the macro as below screenshot:
Regards
Nov 09 2018 12:34 AM
Hi Haytam, good morning and thank you very much for your clear and quick answer.
I have realized that the problem comes from imported data.
Data is not always perfectly written. For instance, "16:40" some times comes as "16 40" or "1640".
So, when executing "text to column", cells having having the "16:40" form are considered as a personalysed form. On the other hand cells having ""16 40" or "1640" are considered as a general form.
If I use your proposed solution and convert everything into text, before changing "16 40" or "1640" into "16:40" it would be very difficult to correct the wrong cells. Is there anyway to deal with this problem?
Thank you very much again
Nov 09 2018 02:59 AM
If so, before you apply the Text to Column process, you have to make the data consistent somehow.
Supposing that these text times always at the beginning of the string, and the third character of the string should always be ( : ), I would suggest this formula:
=IF(MID(A1,3,1)=":",A1,
IF(MID(A1,3,1)=" ",REPLACE(A1,3,1,":"),
IF(AND(MID(A1,3,1)<>" ",MID(A1,3,1)<>":",A1<>""),REPLACE(A1,3,0,":"),"")))
After that, convert the formulas to values, and then apply the text to columns process.
Hope that helps
Nov 09 2018 06:36 AM
Thank you again. it works!
However, would it be possible to run those instructions from a macro command?
Nov 09 2018 08:18 PM - edited Nov 09 2018 08:20 PM
Yes, that possible!
But you cannot record them, you have to craft them as follows:
Sub TextManipulationProcess()
On Error Resume Next
Dim filledCells As Integer
filledCells = Application.WorksheetFunction.CountA(Selection)
Dim cell As Range
For Each cell In Selection
If Mid(cell, 3, 1) = ":" Then
'Do nothing
ElseIf Mid(cell, 3, 1) = " " Then
cell = Application.WorksheetFunction.Replace(cell, 3, 1, ":")
ElseIf Mid(cell, 3, 1) <> " " And Mid(cell, 3, 1) <> ":" And cell <> "" Then
cell = Application.WorksheetFunction.Replace(cell, 3, 0, ":")
Else
cell = ""
End If
Dim counter As Integer
counter = counter + 1
If counter >= filledCells Then
Exit For
End If
Next cell
Dim r As String
r = Selection.Range("A1").Address(False, False)
If Range(r).Value = "" Then Exit Sub
Selection.TextToColumns Destination:=Range(r), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2)), TrailingMinusNumbers:=True
On Error GoTo 0
End Sub
Please note that you have to select the column before you run this code.
Hope that helps
Nov 12 2018 02:11 AM
Good morning Haytam and thank you very much as it worked again!
Do you know why when I try to delete in a column blank spaces Excel does not find any blank spaces? The column has text but it is under the general format. Even if I change the format to text cells, it does not work...
Columns("C:C").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Thank you very much in advance, Juan
Nov 12 2018 08:16 AM
Hi Juan,
There is another type of spaces called non-breaking space, and you need to a special trick to get rid of it.
I think this space is what you have in your worksheet.
You can represent this space using this function:
Chr(160)
And this one for the regular space:
Chr(32)
To remove this space in addition to the regular space, please try this code:
Sub RemoveAllSpacesTypes()
Columns("C:C").Select
Dim spaces As Variant
spaces = Array(Chr(32), Chr(160))
For Each s In spaces
Selection.Replace What:=s, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next s
End Sub
Please note that this code will remove all spaces including the spaces in between words.
Regards
Nov 16 2018 01:09 AM
It worked perfectly. Thank you very much and congratulations!!
Oct 31 2018 10:36 AM - edited Oct 31 2018 10:39 AM
SolutionHi Juan,
I suggest this code:
Sub ReplaceTypos()
Dim typos As Variant
typos = Array("taic", "taihi", "tachi")
For Each typo In typos
Selection.Replace What:=typo, Replacement:="taichi", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
SearchFormat:=False, ReplaceFormat:=False
Next typo
End Sub
This code creates a list of typos and stores it in an array named (typos).
Then it will loop through each typo in typos array and apply the replacement on each one of them.
If you have other typos just expand the typos array list in the code as the syntax below.
typos = Array("typo 1", "typo 2", ...)
Hope that helps