Automatically replace international characters

[honkin]

I have a sheet which holds a large number of football matches and is updated daily with new matches from a file I download. The downside is the file comes with the team names written as they would be in the country of origin, rather than in English.

Here are some examples with the country listed:

K√∏benhavn - AGF (Denmark)
Porto - Famalic√£o (Portugal)
Montedio Yamagata - J√∫bilo Iwata (Japan)

I would prefer to have the erroneous characters replaced with what should be there, like below

Kobenhavn - AGF - Denmark
Porto - Famalicao - Portugal
Montedio Yamagata - Jubilo Iwata - Japan

Is there a way to conditionally format the column (E) or use VBA so that the following happens

√∏ becomes o
√£ becomes a
√∫ becomes u

There are more examples, but once I have a basic formula, I can add any further characters. I want this to happen automatically, so don't want to have to run anything daily, which was why I thought of conditional formatting, but if there is a VBA way of having the replacement happen automatically, that would also be fine

cheers

 

[Zot]

I don't think formula approach is possible because you do not know which accented characters are coming in. It is not like substitute specific character. I think this site will solve your issue:

VBA Convert Foreign/Accent Characters to Plain English Characters

A VBA function to replace foreign characters with the equivalent plain English characters.

www.devhut.net

Ooppps ... I did not notice you are on MacOS.

 

[kweaver]

I was thinking if those characters were Unicode you might do something better than this mess.
I can't imagine continuing w/this if you had even 10 countries.

Book4
	E	F	G
2	K√∏benhavn - AGF (Denmark)		Kobenhavn - AGF - Denmark
3	Porto - Famalic√£o (Portugal)		Porto - Famalicao - Portugal
4	Montedio Yamagata - J√∫bilo Iwata (Japan)		Montedio Yamagata - Jubilo Iwata - Japan
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"√∏","o"),"√£","a"),"√∫","u"),"(","- "),")","")

 

[kweaver]

VBA would be a bit less cumbersome, but I don't think you can do that on a MAC.

Book4
	E	F	G	H	I
2	K√∏benhavn - AGF (Denmark)		Kobenhavn - AGF - Denmark	Kobenhavn - AGF - Denmark	√∏
3	Porto - Famalic√£o (Portugal)		Porto - Famalicao - Portugal	Porto - Famalicao - Portugal	√£
4	Montedio Yamagata - J√∫bilo Iwata (Japan)		Montedio Yamagata - Jubilo Iwata - Japan	Montedio Yamagata - Jubilo Iwata - Japan	√∫
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"√∏","o"),"√£","a"),"√∫","u"),"(","- "),")","")

Sub ReplIntl()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Cells(Rows.Count, "I").End(xlUp).Row
For i = 2 To lr
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 5), "(", "- ")
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), ")", "")

Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), Cells(2, 9), "o")
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), Cells(3, 9), "a")
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), Cells(4, 9), "u")

Next i
End Sub

 

[honkin]

I don't think formula approach is possible because you do not know which accented characters are coming in. It is not like substitute specific character. I think this site will solve your issue:

VBA Convert Foreign/Accent Characters to Plain English Characters

A VBA function to replace foreign characters with the equivalent plain English characters.

www.devhut.net

Ooppps ... I did not notice you are on MacOS.

Cheers and thanks for your reply Zot. There actually aren't any accented characters in the file I download at all. Just the characters I showed and a few others, but nothing above any characters.

 

[honkin]

VBA would be a bit less cumbersome, but I don't think you can do that on a MAC.

Book4
	E	F	G	H	I
2	K√∏benhavn - AGF (Denmark)		Kobenhavn - AGF - Denmark	Kobenhavn - AGF - Denmark	√∏
3	Porto - Famalic√£o (Portugal)		Porto - Famalicao - Portugal	Porto - Famalicao - Portugal	√£
4	Montedio Yamagata - J√∫bilo Iwata (Japan)		Montedio Yamagata - Jubilo Iwata - Japan	Montedio Yamagata - Jubilo Iwata - Japan	√∫
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"√∏","o"),"√£","a"),"√∫","u"),"(","- "),")","")

Sub ReplIntl()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Cells(Rows.Count, "I").End(xlUp).Row
For i = 2 To lr
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 5), "(", "- ")
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), ")", "")

Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), Cells(2, 9), "o")
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), Cells(3, 9), "a")
Cells(i, 8) = WorksheetFunction.Substitute(Cells(i, 8), Cells(4, 9), "u")

Next i
End Sub

cheers Kweaver and thanks for your reply

You approach will undoubtedly work, but adding a further column is not how I want to approach this, unless that is the only way. It is too cumbersome, as I have macros in place which I run on the daily downloaded file; these transfer any autofiltered data which fits the criteria. That downloaded file will NOT have the additional column, so it just creates more work

I tried your formula as a conditional format formula and it did nothing. I applied it to all of column E, but I then discovered that conditional formatting cannot replace the value of a cell

I know sheet-specific macros do work on a Mac, as I have some which change number formats as data is input, so possibly a macro approach will work

This below macro changes any fractions to decimals automatically, though it only does it as you type, not if data is copied and pasted, which is how this football data will come in

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo err_handler
 
  Application.EnableEvents = False
 
  If Target.CountLarge > 1 Then GoTo exit_handler
  If Target = Empty Then GoTo exit_handler
 
  If Not Intersect(Target, Columns("U")) Is Nothing Then
    With Target
      .NumberFormat = "@"
      .Value = CDbl(Evaluate(.Value & "+1"))
      If (.Value - Fix(.Value)) = 0 Then
        .NumberFormat = "0"
      Else
        .NumberFormat = "0.00"
      End If
    End With
  End If
 
exit_handler:
  Application.EnableEvents = True
Exit Sub
 
err_handler:
  MsgBox Err.Number & ": " & Err.Description
  Resume exit_handler
End Sub

So I am a little stuck as to the best way to approach it.

Possible the downloaded file could have a macro run on it which adds a new column, performs the formula you sent and then deletes the original column. This would mean the original file is still in tact and the original macros I already have will still work.

cheers

 

[kweaver]

Since you can run the macro, this modification should just change the E column data with the I column having the things to change.
Of course, you can move those from the I column elsewhere and change the reference in the macro.

I only put the changes in the H column just to parallel the other changes and so I didn't trash them while testing.

Sub ReplIntl()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To lr
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), "(", "- ")
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), ")", "")

Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), Cells(2, 9), "o")
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), Cells(3, 9), "a")
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), Cells(4, 9), "u")

Next i
End Sub

 

[honkin]

Since you can run the macro, this modification should just change the E column data with the I column having the things to change.
Of course, you can move those from the I column elsewhere and change the reference in the macro.

I only put the changes in the H column just to parallel the other changes and so I didn't trash them while testing.

Sub ReplIntl()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To lr
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), "(", "- ")
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), ")", "")

Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), Cells(2, 9), "o")
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), Cells(3, 9), "a")
Cells(i, 5) = WorksheetFunction.Substitute(Cells(i, 5), Cells(4, 9), "u")

Next i
End Sub

Thanks so much Kweaver

Sorry, but you've lost me with the talk of a second column, which seems unnecessary. All the data to be changed will be located in column E, so not sure what column I relates to, as column I will already have other match statistics in it. You mentioned moving those references from column I to another, but I am actually just uncertain what another column is involved for at all. Is this not just a substitute function that is required?

I actually found a solution which will be perfect, as I can call it before all the individual autofilter macros are run

It was simply this, which acts only on the column I am after

Sub RemoveSplChar()
Dim rCell As Range


    For Each rCell In ActiveSheet.Columns("E:E").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        With rCell
            .Value = Application.WorksheetFunction.Substitute(.Value, "√∏", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "√£", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "√∫", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ó", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ő", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ń", "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "√°", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ƒô", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ň", "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ö", "o")
            
            
        End With
                
    Next rCell


End Sub

Thanks so much for your help

This seems to do the trick and I can simply keep adding to it as other ones appear which are not listed

cheers