Excel to extract the letters and Numbers-CodePudding

Table of contents is about

D5-78 - x factory
Xyz company XHJ - 123

Extraction of letters and Numbers only in this way, requirements, remove the Chinese characters,

 Dim I, j, k, h As Integer 
Dim l, m, n As String 


For I=1 To 995 
N="a" 
K=Len (Cells (I, 1)) 
M=Cells (I, 1) 
For j=1 To k 

L=Mid (m, j, 1) 
If LenB (l)=1 Then n=n & amp; L 

Next j 
Sheet2. Cells (I, 2). The Value=https://bbs.csdn.net/topics/n 
Next I

Every time the LenB (l) here, the value is 2, why... Seek expert advice, thank humbly,

CodePudding user response:

995 is a total of 995 records, LenB function idea is to use to judge byte digits, remove the Chinese characters

CodePudding user response:

In VB6 or VBA, not with Len () and LenB () "difference relations" to understand how many Chinese in a string, the number of es,
Because they are using the Unicode format string content, no matter, es, are each character of 2 bytes,
That is to say, the assumption that a string sText, regardless of its content is all Chinese, es, or the content of the mixture of Chinese and western,
LenB (sText) identity in Len (sText) x 2
(even if the string "" air, also accord with the equation: 0=0 x 2)

In addition, your "variable declaration statement" of writing is not correct,
According to your writing, only h, n is the specified data type, are a few other Variant types,

 Private Sub Main () 
'Dim I, j, k, h As Integer 
'Dim l, m, n As String 
% Dim I % % j, k, h As Integer 
Dim m As String 

'For I=1 To 995 
For I=1 To 4 
M=Sheet1 Cells (I, 1). The Text 
H=Len (m) 
For j=1 To h 
If (0 & lt; Asc (Mid $(m, j, 1))) Then the Exit For 
Next 
If (j & gt; H) Then 
M="* * *" 'words are all Chinese 
The Else 
For k=j To h 
If (0 & gt; Asc (Mid $(m, k, 1))) Then the Exit For 
Next 
M=Mid $(m, j, k - j) 
End the If 
Sheet1. Cells (I, 2). The Value=https://bbs.csdn.net/topics/m 
Next 
End Sub 
'the results: 
'D5-78 x factory D5-78 
'the xyz company XHJ XHJ - 123-123 
'the Chinese string test * * * 
'ABC123456 ABC123456

CodePudding user response:

Thank humbly, studied the

CodePudding user response:

 Private Sub Main () 
Dim I %, reg As Object 
The Set reg=CreateObject (" vbscript. RegExp ") 
Reg. Global=True 
Reg. The Pattern="[\ u4e00 - \ u9fa5]" 'matching Chinese are deleted, if only alphanumeric with "\ [^ \ da zA - Z -]" 
For I=1 To 995 
Sheet1. Cells (I, 2). The Value=https://bbs.csdn.net/topics/reg.Replace (Sheet1. Cells (I, 1). The Text, "") 
Next 
The Set of reg=Nothing 
End Sub

Don't give up any opportunity to use a regular, ha ha

CodePudding user response:

 Private Sub Command1_Click () 
Dim strItems (1) As String, strSource As String, strTemp As String 
Dim I As an Integer, j As Integer 

StrItems (0)="D5-78 - x factory" 
StrItems (1)="xyz company XHJ - 123", "

For I=0 To 1 
StrTemp="" 
StrSource=strItems (I) 
For j=1 To Len (strSource) 
If Not Mid (strSource, j, 1) Like "* [! 0-9 a Za - z \] - *" Then strTemp=strTemp & amp; Mid (strSource, j, 1) 
Next j 

MsgBox strTemp 
Next I 


End Sub

CodePudding user response:

In VB6:

 
Dim hz As String 
Dim yw As String 
Dim hzyw As String 
Hz="Chinese characters" 
Yw="English" 
Hzyw="Chinese English" 
The Debug. Print Len (hz), Len (yw), Len (hzyw) 
The Debug. Print LenB (hz), LenB (yw), LenB (hzyw) 
The Debug. Print LenB (StrConv (hz, vbFromUnicode)), LenB (StrConv (yw, vbFromUnicode)), LenB (StrConv (hzyw vbFromUnicode)) 

9 '2 7 
'4 14 of 18 
'
 4 7 11