Special Character codes from API Source

Edsancar

New Member
Joined
Aug 7, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 
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?
 
Upvote 0
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é

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:

VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Assuming the data in Col.A and output the result in col.B.
Code:
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&#233José
András BozóttiAndrás Bozótti
 
Upvote 0

Forum statistics

Threads
1,226,902
Messages
6,193,581
Members
453,808
Latest member
EBERHARDTJOEY

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top