PDA

View Full Version : ASCII replacement



wnazzaro
03-08-2007, 07:43 AM
We are moving data from one system to another. The first system allows non-ascii characters, the second does not. Is there something more elegant that what I have below to find and remove/replace the non-ascii characters?

Thanks,
Bill




For Each c in Range

c = Replace(c, Chr(128), "")
c = Replace(c, Chr(129), "")
c = Replace(c, Chr(130), "")
c = Replace(c, Chr(131), "")
c = Replace(c, Chr(132), "")
c = Replace(c, Chr(133), "")
c = Replace(c, Chr(134), "")
c = Replace(c, Chr(135), "")
c = Replace(c, Chr(136), "")
c = Replace(c, Chr(137), "")
c = Replace(c, Chr(138), "")
c = Replace(c, Chr(139), "")
c = Replace(c, Chr(140), "")
c = Replace(c, Chr(141), "")
c = Replace(c, Chr(142), "")
c = Replace(c, Chr(143), "")
c = Replace(c, Chr(144), "")
c = Replace(c, Chr(145), "")
c = Replace(c, Chr(146), "")
c = Replace(c, Chr(147), "")
c = Replace(c, Chr(148), "")
c = Replace(c, Chr(149), "")
c = Replace(c, Chr(150), "")
c = Replace(c, Chr(151), "")
c = Replace(c, Chr(152), "")
c = Replace(c, Chr(153), "")
c = Replace(c, Chr(154), "")
c = Replace(c, Chr(155), "")
c = Replace(c, Chr(156), "")
c = Replace(c, Chr(157), "")
c = Replace(c, Chr(158), "")
c = Replace(c, Chr(159), "")
c = Replace(c, Chr(160), "")
c = Replace(c, Chr(161), "")
c = Replace(c, Chr(162), "")
c = Replace(c, Chr(163), "")
c = Replace(c, Chr(164), "")
c = Replace(c, Chr(165), "")
c = Replace(c, Chr(166), "")
c = Replace(c, Chr(167), "")
c = Replace(c, Chr(168), "")
c = Replace(c, Chr(169), "")
c = Replace(c, Chr(170), "")
c = Replace(c, Chr(171), "")
c = Replace(c, Chr(172), "")
c = Replace(c, Chr(173), "")
c = Replace(c, Chr(174), "")
c = Replace(c, Chr(175), "")
c = Replace(c, Chr(176), "")
c = Replace(c, Chr(177), "")
c = Replace(c, Chr(178), "")
c = Replace(c, Chr(179), "")
c = Replace(c, Chr(180), "")
c = Replace(c, Chr(181), "")
c = Replace(c, Chr(182), "")
c = Replace(c, Chr(183), "")
c = Replace(c, Chr(184), "")
c = Replace(c, Chr(185), "")
c = Replace(c, Chr(186), "")
c = Replace(c, Chr(187), "")
c = Replace(c, Chr(188), "")
c = Replace(c, Chr(189), "")
c = Replace(c, Chr(190), "")
c = Replace(c, Chr(191), "")

'With ASCII replacement
c = Replace(c, Chr(192), "A")
c = Replace(c, Chr(193), "A")
c = Replace(c, Chr(194), "A")
c = Replace(c, Chr(195), "A")
c = Replace(c, Chr(196), "A")
c = Replace(c, Chr(197), "A")
c = Replace(c, Chr(198), "AE")
c = Replace(c, Chr(199), "C")
c = Replace(c, Chr(200), "E")
c = Replace(c, Chr(201), "E")
c = Replace(c, Chr(202), "E")
c = Replace(c, Chr(203), "E")
c = Replace(c, Chr(204), "I")
c = Replace(c, Chr(205), "I")
c = Replace(c, Chr(206), "I")
c = Replace(c, Chr(207), "I")
c = Replace(c, Chr(208), "D")
c = Replace(c, Chr(209), "N")
c = Replace(c, Chr(210), "O")
c = Replace(c, Chr(211), "O")
c = Replace(c, Chr(212), "O")
c = Replace(c, Chr(213), "O")
c = Replace(c, Chr(214), "O")
c = Replace(c, Chr(215), "x")
c = Replace(c, Chr(216), "O")
c = Replace(c, Chr(217), "U")
c = Replace(c, Chr(218), "U")
c = Replace(c, Chr(219), "U")
c = Replace(c, Chr(220), "U")
c = Replace(c, Chr(221), "Y")
c = Replace(c, Chr(222), "Th")
c = Replace(c, Chr(223), "ss")
c = Replace(c, Chr(224), "a")
c = Replace(c, Chr(225), "a")
c = Replace(c, Chr(226), "a")
c = Replace(c, Chr(227), "a")
c = Replace(c, Chr(228), "a")
c = Replace(c, Chr(229), "a")
c = Replace(c, Chr(230), "ae")
c = Replace(c, Chr(231), "c")
c = Replace(c, Chr(232), "e")
c = Replace(c, Chr(233), "e")
c = Replace(c, Chr(234), "e")
c = Replace(c, Chr(235), "e")
c = Replace(c, Chr(236), "i")
c = Replace(c, Chr(237), "i")
c = Replace(c, Chr(238), "i")
c = Replace(c, Chr(239), "i")
c = Replace(c, Chr(240), "d")
c = Replace(c, Chr(241), "n")
c = Replace(c, Chr(242), "o")
c = Replace(c, Chr(243), "o")
c = Replace(c, Chr(244), "o")
c = Replace(c, Chr(245), "o")
c = Replace(c, Chr(246), "o")
c = Replace(c, Chr(247), " ")
c = Replace(c, Chr(248), "o")
c = Replace(c, Chr(249), "u")
c = Replace(c, Chr(250), "u")
c = Replace(c, Chr(251), "u")
c = Replace(c, Chr(252), "u")
c = Replace(c, Chr(253), "y")
c = Replace(c, Chr(254), "th")
c = Replace(c, Chr(255), "y")
Next c

CBrine
03-08-2007, 02:12 PM
This part

c = Replace(c, Chr(128), "")
c = Replace(c, Chr(129), "")
c = Replace(c, Chr(130), "")
c = Replace(c, Chr(131), "")
c = Replace(c, Chr(132), "")
c = Replace(c, Chr(133), "")
c = Replace(c, Chr(134), "")
c = Replace(c, Chr(135), "")
c = Replace(c, Chr(136), "")
c = Replace(c, Chr(137), "")
c = Replace(c, Chr(138), "")
c = Replace(c, Chr(139), "")
c = Replace(c, Chr(140), "")
c = Replace(c, Chr(141), "")
c = Replace(c, Chr(142), "")
c = Replace(c, Chr(143), "")
c = Replace(c, Chr(144), "")
c = Replace(c, Chr(145), "")
c = Replace(c, Chr(146), "")
c = Replace(c, Chr(147), "")
c = Replace(c, Chr(148), "")
c = Replace(c, Chr(149), "")
c = Replace(c, Chr(150), "")
c = Replace(c, Chr(151), "")
c = Replace(c, Chr(152), "")
c = Replace(c, Chr(153), "")
c = Replace(c, Chr(154), "")
c = Replace(c, Chr(155), "")
c = Replace(c, Chr(156), "")
c = Replace(c, Chr(157), "")
c = Replace(c, Chr(158), "")
c = Replace(c, Chr(159), "")
c = Replace(c, Chr(160), "")
c = Replace(c, Chr(161), "")
c = Replace(c, Chr(162), "")
c = Replace(c, Chr(163), "")
c = Replace(c, Chr(164), "")
c = Replace(c, Chr(165), "")
c = Replace(c, Chr(166), "")
c = Replace(c, Chr(167), "")
c = Replace(c, Chr(168), "")
c = Replace(c, Chr(169), "")
c = Replace(c, Chr(170), "")
c = Replace(c, Chr(171), "")
c = Replace(c, Chr(172), "")
c = Replace(c, Chr(173), "")
c = Replace(c, Chr(174), "")
c = Replace(c, Chr(175), "")
c = Replace(c, Chr(176), "")
c = Replace(c, Chr(177), "")
c = Replace(c, Chr(178), "")
c = Replace(c, Chr(179), "")
c = Replace(c, Chr(180), "")
c = Replace(c, Chr(181), "")
c = Replace(c, Chr(182), "")
c = Replace(c, Chr(183), "")
c = Replace(c, Chr(184), "")
c = Replace(c, Chr(185), "")
c = Replace(c, Chr(186), "")
c = Replace(c, Chr(187), "")
c = Replace(c, Chr(188), "")
c = Replace(c, Chr(189), "")
c = Replace(c, Chr(190), "")
c = Replace(c, Chr(191), "")

Can be replaced with


Dim count as integer

for count = 128 to 191
c=replace(c,chr(count),"")
Next count


The other part, I'm not sure.
HTH
Cal

wnazzaro
03-08-2007, 02:49 PM
You know, I've been looking for something that would do the whole job and I missed what you suggested which makes the code cleaner. Thanks.

moa
03-09-2007, 02:22 AM
You could also do the upper and lower case stuff at the same time.


Const inc As integer = 32

For X = 192 To 197
c = Replace(c, Chr(X), "A")
c = Replace(c, Chr(X + inc), "a")
Next

you could do something like this for each vowel but yeah, can't really do it all in one go.

JimmyTheHand
03-10-2007, 09:18 AM
Hi :hi:

This function
Function AsciiChar(Code As Long)
Const BaseStr = " A A A A A AAE C E E E E I I I I D N O O O O O x O U U U U YThss a a a a a aae c e e e e i i i i d n o o o o o o u u u u yth y"
Dim C As Long
C = (Code.Value - 192) * 2
AsciiChar = Trim(Mid(BaseStr, C + 1, 2))
End Function

in conjunction with
Dim count As Long

For count = 192 To 255
c=replace(c,Chr(count),AsciiChar(count))
Next count

might help with the "other" part :)

Jimmy