[已解决]拼音简码问题！C列内容包含【 】 就会转换错误~！求助

80013025

拼音简码问题！C列内容包含【 】 就会转换错误~！求助
拼音简04.rar (22.54 KB, 下载次数: 13)

2016-4-22 19:56 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

zjdh

2016-4-22 20:54

Function 拼音简码(汉字 As Variant)
     Dim hz As String
     汉字 = Replace(Replace(汉字, "【", ""), "】", "")
     hz = StrConv(汉字, vbNarrow)
     For i = 1 To Len(hz)
         If Asc(Mid(hz, i, 1)) = AscB(Mid(hz, i, 1)) Then
             拼音简码 = 拼音简码 & UCase(Mid(hz, i, 1))
         Else
             拼音简码 = 拼音简码 & Application.VLookup(Mid(hz, i, 1), [{"阿","A";"啊","A";"巴","B";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"七","Q";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"西","X";"昔","X";"压","Y";"匝","Z"}], 2)
         End If
     Next i
End Function

跳转到最佳答案楼层

fggf

“【”或“】”是固定在最后一个字符吗
是的话用这个：=IF(ISERR(拼音简码(C2)),拼音简码(LEFT(C2,LEN(C2)-1)),拼音简码(C2))下拉

蝶·舞

嵌套substitute替换一下

80013025

fggf 发表于 2016-4-22 20:24
“【”或“】”是固定在最后一个字符吗
是的话用这个：=IF(ISERR(拼音简码(C2)),拼音简码(LEFT(C2,LEN(C2) ...

【】不固定，以下代码如增加到红色位置就提示 太长。有没有办法解决？

Function 拼音简码(汉字 As Variant)
     Dim hz As String

     hz = StrConv(汉字, vbNarrow)
     For i = 1 To Len(hz)
         If Asc(Mid(hz, i, 1)) = AscB(Mid(hz, i, 1)) Then
             拼音简码 = 拼音简码 & UCase(Mid(hz, i, 1))
         Else
             拼音简码 = 拼音简码 & Application.VLookup(Mid(hz, i, 1), [{"阿","A";"啊","A";"巴","B";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"七","Q";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"西","X";"昔","X";"压","Y";"匝","Z";"【","";"】",""}], 2)
         End If
     Next i
End Function

zjdh

Function 拼音简码(汉字 As Variant)
     Dim hz As String
     汉字 = Replace(Replace(汉字, "【", ""), "】", "")
     hz = StrConv(汉字, vbNarrow)
     For i = 1 To Len(hz)
         If Asc(Mid(hz, i, 1)) = AscB(Mid(hz, i, 1)) Then
             拼音简码 = 拼音简码 & UCase(Mid(hz, i, 1))
         Else
             拼音简码 = 拼音简码 & Application.VLookup(Mid(hz, i, 1), [{"阿","A";"啊","A";"巴","B";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"七","Q";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"西","X";"昔","X";"压","Y";"匝","Z"}], 2)
         End If
     Next i
End Function

zjdh

或则
Function 拼音简码(汉字 As Variant)
     Dim hz As String
     hz = StrConv(汉字, vbNarrow)
     For i = 1 To Len(hz)
         If InStr("【】", Mid(hz, i, 1)) Then GoTo 10
         If Asc(Mid(hz, i, 1)) = AscB(Mid(hz, i, 1)) Then
             拼音简码 = 拼音简码 & UCase(Mid(hz, i, 1))
         Else
             拼音简码 = 拼音简码 & Application.VLookup(Mid(hz, i, 1), [{"阿","A";"啊","A";"巴","B";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"七","Q";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"西","X";"昔","X";"压","Y";"匝","Z"}], 2)
         End If
10     Next i
End Function

zjdh

发现你那个被搜索数据有重复，修改如下
Function 拼音简码(汉字 As Variant)
    Dim hz As String
    hz = StrConv(汉字, vbNarrow)
    For i = 1 To Len(hz)
        If Asc(Mid(hz, i, 1)) = AscB(Mid(hz, i, 1)) Then
            拼音简码 = 拼音简码 & UCase(Mid(hz, i, 1))
        Else
            拼音简码 = 拼音简码 & Application.VLookup(Mid(hz, i, 1), [{"【","";"】","";"啊","A";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"七","Q";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"西","X";"昔","X";"压","Y";"匝","Z"}], 2)
        End If
    Next i
End Function

80013025

zjdh 发表于 2016-4-22 21:34
发现你那个被搜索数据有重复，修改如下
Function 拼音简码(汉字 As Variant)
    Dim hz As String

谢谢老师帮忙修改


啊 可以被识别字母 A
阿 就识别不了

芭 可以被识别字母B
巴 就识别不了

红色部分都是，识别不了字母的。
[{"阿","A";"啊","A";"巴","B";"芭","B";"擦","C";"搭","D";"蛾","E";"发","F";"噶","G";"哈","H";"击","J";"喀","K";"垃","L";"妈","M";"拿","N";"哦","O";"啪","P";"七","Q";"期","Q";"然","R";"撒","S";"塌","T";"挖","W";"西","X";"昔","X";"压","Y";"匝","Z"}], 2)