-1

I have kind of specific problem at work. I receive XLS file that has content in japanese characters and also alphabet letters and numbers, but all in full-width Japanese format. My problem is that Excel does not recognize these as normal letters and numbers. I found webpage with converter full-width <--> half-width characters, but it is too slow to manually copy, paste and overwrite each cell. There is also a JAVA .jar file that supposedly does the same converting (Class Transliterator). I wish to find a way how to run this Java function for each cell in defined range and return its value in half-width (normal) characters in order to create half-width copy of the original XLS file.

Actual example of cell value is like this: Original cell content: ＃０１Ａ－１１０ Desired cell content after conversion: #01A-110

Thank you. PS. I know how to loop cells and everything I need for it in VBA, but what I cannot find is bridging the JAVA that returns new value (as string).

jirka k
  • 1
  • 1
  • Take a look at this page, to see if it is what you are looking for. (https://stackoverflow.com/questions/32303509/how-to-convert-from-full-width-to-half-width-japanese-characters-in-java) – Mitch Oct 14 '17 at 17:01
  • the accepted answer at this link shows what you are looking for https://stackoverflow.com/questions/11343769/microsoft-excel-macro-to-run-java-program#11353148 – jsotola Oct 14 '17 at 20:11

1 Answers1

0

Actually, I found solution within VBA, because running .JAR turned out to be impossible.

The solution is StrConv( String, Conversion, [LocaleID] )

VBA selective conversion of double-byte to single-bye characters

Important: When in different lang environment, there must be LocaleID set to corresponding character set via [LocaleID] - An optional argument, specifying the LocaleID.

For ID codes see https://support.microsoft.com/cs-cz/help/221435/list-of-supported-locale-identifiers-in-word

Working code then looks like this:

Sub zen2han()

Dim CLRange As Range

For Each CLRange In Selection

CLRange.Value = StrConv(CLRange.Value, vbNarrow, 1041)

Next CLRange

End Sub

jirka k
  • 1
  • 1