Convert two-byte numbers to one-byte?

Hi, all!

I use Japanese text in Excel 2004 for Mac. (Please note the "for Mac." Thanks. ^_^)

I need some way to convert all the two-byte numbers (that is, Hindu-Arabic numbers typed from the Japanese keyboard) in a worksheet to one-byte numbers (Hindu-Arabic numbers typed from the English keyboard). I couldn't find any code in the Help for VB that allowed a search for such numbers, although there is a string conversion constant for it once I find how to construct a search (vbNarrow converts double-byte characters to single-byte ones). I tried recording a macro (I usually do this to get a starting point for coding), but did not get anything useful: the code only seemed to think I was setting the font and general formatting (e.g., strike-through or not).

I also tried checking the AppleScript commands available for Excel (since I access the Mac input system to do the conversion on manually selected text), but did not see anything of help there either.

Anyone have any ideas?

Thank you!

ctap

Hello CheaperThanAPro,

I think you would have to construct a macro to do the numeric conversions. If you can obtain the Unicode codes for the numbers then you can set up reference arrays to translate the character codes into the right number. I know Japanese, but not Hebrew or Arabic. I know how to obtain the ASCII code for a character, but not for Unicode, since Unicode characters may not be grouped in the same block. I don't have a reference manual for Unicode 5.0 to look it up for you. The Unicode home page http://www.unicode.org/ isn't particularly easy to use either. I would either buy the book or find someone whose one before attempting to code a translating macro. Sorry, I couldn't be helpful.

Sincerely,
Leith Ross

I'm sorry, I suppose I should've defined my terms. "Hindu-Arabic numerals" are the figures widely used in mathematics--0123456789--so-called because they were invented by the Hindus and came to Western Europe via the Arabs, who made some changes to them. As opposed to Roman numerals, kanji numerals, etc.

I'm not sure that the numbers are Unicode in Excel for Mac. I think they may be SJIS. I can't even coax that much information out of my research.

Thanks!

ctap

I am feeling very stupid. I was thinking to go searching for strings of two-byte numerals and then convert them to one-byte. It never occurred to me to attack it digit by digit.

The Replace command works just fine if I tell it to do ten separate global replacements, with each two-byte digit as the What and each corresponding one-byte digit as the replacement. Daft of me not to see it sooner.

Thanks for the prod, Leith! I wouldn't have thought of the solution without your suggestion.

ctap