Special Character codes from API Source

[Edsancar]

I'm accesing a JSON API frontend with VBA and VBA-JSON v2.3.1.

The issue I'm facing is that the API database is sending me character code for special characters thus I get
José
instead of José

Is there a way I can solve this?

Thanks for your help.

 

[Micron]

Maybe like "Jos" & Chr(233). So a code or formula needs to remove the ampersand and octothorpe as well. Can you use code for the solution?

 

[Micron]

Out of curiosity I wrote this. If you can use it, put it in a standard module. If you call it with a formula in a helper column as
= GetAlpha(A2) where A2 contains José it should return José

Function GetAlpha (strIn As String)
Dim i As Integer

strIn = Replace(Replace("José", "&#", ""), ";", "")
i = 1
Do Until IsNumeric(Mid(strIn, i, 1))
    i = i + 1
Loop

strIn = Left(strIn, i - 1) & Chr(Mid(strIn, i))
GetAlpha = strIn

End Function

 

[Edsancar]

Thanks for your prompt response two questions on the solution:

Just need to add some information firts
I'm importing 840 sets of info divided in 21 groups of 40 each. The full name is in one string and additional info, that I need to extract using simple VLOOKUP is added on separte cells
All of them use latin alpahbets, but sonce they have many nationalities I have a full array of different special characters.

In this case I maybe simpliefied the name too much

The name in this case would be

José Sainz meaning José Sainz
but I also have
András Bozótti that would be András Bozótt

Woultd your solution work in both cases (I assume I would need to rework every single name)

Thanks

 

[Micron]

What I posted would not work for that without being modified. You'd need to clarify some things first. Are these the only special characters you have:
& # ;
What happened to the i in 243;tti? Or did you mean Bozótti?

Shouldn't be too hard to adapt that code but please review your data and look for other characters, such as ? @ $ etc. I'm thinking it would not matter if the string was longer (e.g. where your data included a middle name or initial, so 3 parts, not 2. However the code would have to maintain your space(s) between names, yes?

 

[Micron]

Seems I can't help myself
I went ahead anyway, so you could try the following (only try code you're given on a copy of your workbook until you're satisfied it won't ruin anything).
Note- you need a reference to Microsoft VBScript Regular Expressions for this. The called function I adapted from here:

How to extract numbers from a text string in VBA

I need to extract the numbers from a string of text and I'm not quite sure how to do it. The code I've attached below is very preliminary and most likely can be done more elegantly. A sample of the

stackoverflow.com

Function getAlpha(strIn As String)
Dim i As Integer
Dim ary() As String, strResult As String

strIn = Replace(strIn, "&", "")
strIn = Replace(strIn, "#", "")
strIn = Replace(strIn, ";", "")
strResult = ExtractNumbers(strIn)
ary = Split(strResult, ",")
For i = 0 To UBound(ary)
    strIn = Replace(strIn, ary(i), Chr(ary(i)))
Next
getAlpha = strIn

End Function

Function ExtractNumbers(strIn As String) As String
Dim rgex As RegExp, matches As MatchCollection, match As match

Set rgex = New RegExp
rgex.Pattern = "\d+"
rgex.Global = True

If (rgex.test(strIn) = True) Then
    Set matches = rgex.Execute(strIn)
    For Each match In matches
        ExtractNumbers = ExtractNumbers & match & ","
    Next
End If
ExtractNumbers = Left(ExtractNumbers, Len(ExtractNumbers) - 1)

End Function

Call from cell formula in B2: =GetAlpha(A2)
A2 contains András Bozótti
Result in B2: András Bozótti

See if that helps.

 

[Edsancar]

What I posted would not work for that without being modified. You'd need to clarify some things first. Are these the only special characters you have:
& # ;

Yes only those apart from the three digit code there are no numbers or punctuation marks it is mostly accents and characters as ç

Names are normally 2 words long bt there are a couple that have 3 and and odd 4 words

What happened to the i in 243;tti? Or did you mean Bozótti?

You are right, It's a typo, sorry

I'll try the code and let you know.

 

[Edsancar]

I tested the code you provided and have an issue:

I first added the Microsoft VBScript Regular expressions for it to work.

I had to add and "=IF" since if the name doesn't contain any special character codes it gives a "#VALUE!"error

I addded this instruction on a table that simply makes a new copy of the table I create from API.

=IF(ISNUMBER(SEARCH("&",D5)),getAlpha(D5),D5) were D5 contains the cell I want to correct, It works but but I'm getting "#VALUE!" errors on some:
Marius Ruţa that is Marius Ruţa
Atis Praudiņš that is Atis Praudiņš
Michał Sułek that is Michał Sułek
Mantas Šeštokas that is Mantas Šeštokas

thanks

 

[Micron]

since if the name doesn't contain any special character codes

Again, you'll have to post information about all the variances you might have. Perhaps I should have waited for you to do that rather than jumping ahead. It stands to reason that if a cell value does not contain any odd strings, then what I wrote won't work, but I can only go by what you post in the way of details. If you want to incorporate Excel functions into a formula that calls code that I post e.g.

=IF(ISNUMBER(SEARCH("&",D5))

I don't think that I can help. I'm primarily an Access VBA guy who dabbles in Excel VBA, and I have no desire to elevate my knowledge of Excel formulas beyond those that are very basic. So if you have anomalies that need to be dealt with, it's likely that I can only help with those in the code itself and not in the formula that calls it.

 

[Fuji]

Assuming the data in Col.A and output the result in col.B.

Sub test()
    Dim a, i&, x&, s$, temp&
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        For i = 1 To UBound(a, 1)
            s = a(i, 1)
            x = InStr(s, "&#")
            Do While x
                temp = Val(Mid$(s, x + 2))
                s = Application.Replace(s, x, 2 + Len(temp), WorksheetFunction.Unichar(temp))
                x = InStr(x + 1, s, "&#")
            Loop
            a(i, 2) = s
        Next
        .Resize(, 2) = a
    End With
End Sub

Got

Jos&#233	José
András Bozótti	András Bozótti