I'm stupid! Only 4 digits and 5 digit filter again later with formula,
But still mangled!
After the original LE reference 4 digits, macro formula ok
I thought for a moment, all of the specifications of the application should be a "?" On behalf of the characters
When the data in the LEC??????? Containing the letter "C"=inorganic stone,
When the data in the LEY??????? Containing the letter "Y=" quartz "
When the data in the LEYC??????? Contain letters "YC=" inorganic quartz, "
When the data in the LE??????? Behind A or B (characters) containing the letter "A or B=" super "when data in LE????/??????? (4 characters or 5 characters), because it will have some data in the data, to 2,
So the following rules is excluding the character after the "-"
If there is data for LE21335-2 and LE1325-2 need to specify "- 2" before the number of characters,
When 4 characters after LE, look at the second digit to values such as: (LE2105=single, LE2306=3, LE3255=double)
When after LE 5 characters, look at the third digit to values such as: (LE20105=single, LE21306=3, LE21255=double)
If only letters behind only 5 digits I also changed the formula, but the actual operation process, no, wrong data
And if not in the mixed data
4 digit after the original LE rules: (old)
The capital letters "LE after the second number is three means:" three "
The capital letters "LE after the second number is 1 means: single
After the capital letters "LE, the second number is 2's representative: double
The number of the first after the capital letters "LE is C means: inorganic stone
The number of the first after the capital letters "LE is Y means: quartz
Any digits after the capital letters "LE also have capital letters" A or B ":"
Any digits after the capital letters "LE also have capital letters" Y or S ": the element
Sub sorting data ()
Dim m %
M=[a65536]. End (3). The Row - 1
Arr=Range (a2: "a" & amp; M + 1)
ReDim arr1 (1 To m, 1 To 1)
For I=1 To m
If Mid (arr (I, 1), 4, 1)=1 Then
Arr1 (I, 1)="single"
End the If
If Mid (arr (I, 1), 4, 1)=2 Then
Arr1 (I, 1)="double"
End the If
If Mid (arr (I, 1), 4, 1)=3 Then
Arr1 (I, 1)="three"
End the If
If Mid (arr (I, 1), 3, 1)="C" Then
Inorganic stone arr1 (I, 1)=""
End the If
If Mid (arr (I, 1), 3, 1)="Y" Then
Arr1 (I, 1)="quartz"
End the If
For j=4 To Len (arr (I, 1))
If Mid (arr (I, 1), j, 1)="Y" Or Mid (arr (I, 1), j, 1)="S" Then
Arr1 (I, 1)="elements"
End the If
If Mid (arr (I, 1), j, 1)="A" Or Mid (arr (I, 1), j, 1)="B" Then
Arr1 (I, 1)=", "
End the If
Next
Next
[b2]. Resize (m, 1)=arr1
End Sub
CodePudding user response:
This BBS very few people use the