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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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

Well-known Member
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,887

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

Well-known Member
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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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

Well-known Member
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,613
Messages
5,832,710
Members
430,155
Latest member
spacedad41

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
Top