Translating UTF-8 to legible Latin text

[Rhodie72]

I have data that is encoded in utf-8 that appears as webdings but cannot be read. It is english text for sample payslips. The copied data looks like this:

  
What it actually says is:

DIAMOND BUS LIMITED

What I would like to do is translate it into legible latin text using VBA as the Excel UTF-8 import does NOT correctly do the job at all. I know for a fact a solution to this will help thousands of people globally.
Whilst Excel can convert the orignal text into binary numbers, it is simply a mathematical case of replacing the UTF-8 characters with Latin charaters from standard code pages

My idea is to convert all the characters into binary

i BEGAN A TABLE FOR TRANSLATION BUT vba IS THE WAY FORWARD. Here's what I have done so far:

Sub Translate()
    For i = 1 To Len(ActiveCell.Value)
        answer = Application.WorksheetFunction.Unicode(Mid(ActiveCell.Value, i, 1))
        MsgBox answer
    Next i
End Sub

Putting the resultant info into a dynamic array and then converting the resultant into letters would be excellent.

Please help?

In Excel it looks like this:

 

[rlv01]

You could handle the A:G range this way.

Sub UniConvert2()
    Dim CA As Variant
    Dim CCol As Long, RRow As Long, I As Long
    Dim CellRange As Range
    Dim WS As Worksheet
    Dim S As String

    Set WS = ActiveSheet

    With WS
        'Set CellRange = .Range("A1:G33")
        Set CellRange = .Range("A1:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    CA = CellRange.Value
    For RRow = LBound(CA, 1) To UBound(CA, 1)
        For CCol = LBound(CA, 2) To UBound(CA, 2)
            S = ""
            For I = 1 To Len(CA(RRow, CCol))
                S = S & Chr(Application.Unicode(Mid(CA(RRow, CCol), I, 1)) And 255)
            Next I
            CA(RRow, CCol) = S
        Next CCol
    Next RRow
    CellRange.Value = CA
End Sub

 

[Rhodie72]

I finally got it perfected. So for all those who want to import ilegible UTF-8 inot excel from text sources, be it pasted or using text import wizard, or whatever, here is the solution with many thanks to @rlv01.
Make sure you create a worksheet called "UTF-8" and import/paste the data there. This translates theregion of text regardless of its size and location you choose.
If there are too many characters in a cell then it may hit a snag (255+, I believe) but this was not encountered in my case as lengthof strings were within limits.

Sub Translate_UTF_8()
    Dim CA As Variant,CCol As Long, RRow As Long, I As Long, CellRange As Range,WS As Worksheet, S As String

    Set WS = Sheets("UTF-8") 'A sheet in the workbook that only handles the translation

    With WS
        Set CellRange = ActiveCell.CurrentRegion 'Comment out if using set ranges
        'Set CellRange = .Range("A1:AZ43543" & .Range("A" & .Rows.Count).End(xlUp).Row) 'Customise as necessary if you don't wan to use seperated regions
    End With

    CA = CellRange.Value
    For RRow = LBound(CA, 1) To UBound(CA, 1)
        For CCol = LBound(CA, 2) To UBound(CA, 2)
            S = ""
            For I = 1 To Len(CA(RRow, CCol))
                S = S & Chr(Application.Unicode(Mid(CA(RRow, CCol), I, 1)) And 255)
            Next I
            CA(RRow, CCol) = S
        Next CCol
    Next RRow
    CellRange.Value = CA
End Sub

 

[smozgur]

I finally got it perfected.

As I can see from the thread (and also as confirmed in post #9), post #11 is the actual answer for this question. Any perfection according to the specific range and/or structure (like referencing a certain sheet instead of active sheet) could be applied separately. Therefore, I switched the solution post since the generic version will provide more help to future readers.