Unusual non english characters in vba

lotsbg

Board Regular
Joined
Mar 8, 2004
Messages
110
I need to type the following word in VBA; Příbram, which obviously uses a couple of wierd characters. When I copy and paste the word into VBA I end up with P?íbram.

Obviously VBA doesn't recognise the ř symbol. I have tens of other words like this. Is there anyway for VBA to recognise these symbols somehow?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
You say "non English" characters - can you say which language it is, please?
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
Hi,

Excel usually recognizes only ASCII codes from 0 to 255. Characters such as ř exceed the ASCII code 255 and so you cannot use these like you would normally do. (If you type ř in A1, and use =CODE(A1), you will get 63 which is the ASCII code for ?)

To use this in VBA you will need to use ChrW() function. Go to the character map via Start | Accessories | System Tools | Character Map.

You will need to search for the Hex code of the characters (it is 0159 for ř). Then you will need to convert the Hex number to decimal number and use that in ChrW. The decimal equivalent for Hex 0159 is 345.

So, if you use

Range("A1").Value = ChrW(345) you will get ř in A1.

So you need to use "P" & ChrW(345) & "íbram" etc....

Edit: I guess an easier way to get the decimal codes would be to use a simple UDF...<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ASCIICode(Character<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br>    ASCIICode = AscW(Character)<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>
Excel Workbook
ABC
1C63268
2r63345
3O63334
4N63327
5a63259
6?63330
7Z63377
8O63465
9?63912
10?63510
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B1=CODE(A1)
C1=ASCIICode(A1)
 
Last edited:

ravi4ever

Active Member
Joined
Apr 13, 2010
Messages
316
yes nicely explained, you can also get the Char code from Excel itself> Go to Insert Menu>Symbol>Change the value of from drop down list(right bottom) to "Unicode (Hex)" it will show you code in the box just right to it..
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,791

ADVERTISEMENT

Sandeep already explained well how to use the Unicode characters. Just a small remark: you don't have to convert hex->decimal, you can use directly a hex number in vba preceeding it with "&H".

Ex., you find ř in the map as 159 hex, you can use:

Range("A1").Value = "P" & ChrW(&H159) & "íbram"
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
Thanks for this info Pedro. I wish I had known this a couple of years back when I wrote a code to replace such characters with regular Latin characters ... Spent a lot of time converting from Hex to Dec :biggrin:
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows

ADVERTISEMENT

I would be curious to know if anyone has tried this utility (it looks like it could be useful but I've never downloaded it):
http://teaandbiscuits.org.uk/drupal/node/77
 

lotsbg

Board Regular
Joined
Mar 8, 2004
Messages
110
wow thanks so much guys, you have been a massive massive help.
Much appreciated.
 

gonen

New Member
Joined
Mar 6, 2011
Messages
9
My question is:

I see ? in a string. I know its the character ø (Nordic character)

I would like to translate the ø character to english o - using

Code:
newstr = replace(oldstr,"[COLOR="red"][SIZE="4"]ø[/SIZE][/COLOR]","[COLOR="red"][SIZE="4"]o[/SIZE][/COLOR]")
How can I achieve this ?

The VBA msgbox and debug.print show always ? but the real value is in the string variable because when I display the string in a userform textbox I see the real character ø

Plase advice

thank you !!!
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
This looks much like the Greek ø, for which the ASCII code is 248

If it is not Phi, then use the function posted by me earlier in the thread to get the ASCII code for the character.

You can then use that to replace it.

Replace(oldstr,ChrW(.....),o)

.... is the ASCII code.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,816
Messages
5,513,554
Members
408,957
Latest member
Jcoverick

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top