I have posted the article Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′ that caused incomplete result. After post it, I have found good article in Japanese. Therefore, I gave it additional value in English.
Standard Tables of Food Composition in Japan 2010
Make a new EXCEL Book. Copy text from the PDF files (‘1299012_1.pdf’ to ‘1299012_18.pdf’) and option paste to EXCEL worksheet ‘Sheet1’ continuously without blank line between the last line of previous text and the first line of next text. With ‘Text File Wizard’, change option data type of the first column to ‘String’. Download Academic name of food materials, select all text and paste to Sheet2. In the first tab of ‘Text File Wizard’, select option ‘The data field separated by delimiters such as comma or tab’. In second tab, remove check mark ‘Consider continuous delimiters as one’. In the last tab, change option data type of the first column to ‘String’. Save as ‘Sample.xlsm’.
Press ‘Alt’ key and ‘F11’ key to launch VBE. Insert module and paste following code. Run ‘Separate_by_Parent’ procedure.
Option Explicit
Function MajorCategoryAr(ByRef Sh As Worksheet) As String()
Dim mySht As Worksheet
Dim myRng As Range
Dim tmpAr As Variant
Dim StartEnd As Variant
Dim strFoodGroup As String
Dim strFoodGroupJP As String
Dim strFoodGroupEN As String
Dim strSubFoodGroup As String
Dim strSubFoodGroupJP As String
Dim strSubFoodGroupEN As String
Dim strSub_Category As String
Dim strSub_CategoryJP As String
Dim strSub_CategoryEN As String
Dim strMajor_Category As String
Dim StartNumber() As String
Dim Exit_Number() As String
Dim FoodGroupJP() As String
Dim FoodGroupEN() As String
Dim Sub_FoodGroup_JP() As String
Dim Sub_FoodGroup_EN() As String
Dim Sub_Category_JPN() As String
Dim Sub_Category_ENG() As String
Dim Major_CategoryJP() As String
Dim Major_CategoryEN() As String
Dim Major_CategoryLT() As String
Dim myArray() As String
Dim i As Long
Dim j As Long
Dim k As Long
Dim n As Long
Dim RegExp_3_Digit_Num As Object
Dim RegExp_Item_Number As Object
Dim RegExp_SentakuHanni As Object
Dim RegExp_SubCategory1 As Object
Dim RegExp_SubCategory2 As Object
Dim RegExp_MedCategory As Object
Dim RegExp_Foods_Group As Object
Dim RegExp_Jpn_Eng_Mix As Object
Dim RegExp_JapaneseOnly As Object
Dim RegExp_Upper_Lower As Object
Dim RegExp_Upper_Only As Object
Dim RegExp_Lower_Only As Object
Dim RegExp_RoundBracket As Object
Dim RegExp_SquareBracket As Object
Dim RegExp_AngleBracket As Object
Dim myMatches As Object
Dim myMatch As Object
Const Ptn_3_Digit_Num As String = "[0-9]{3}$"
Const Ptn_Item_Number As String = "^[0-9]{5}$"
Const Ptn_SentakuHanni As String = "(，|～)"
Const Ptn_SubCategory1 As String = "^(（|\().(）|\))$"
Const Ptn_SubCategory2 As String = "^(＜|＞)$"
Const Ptn_MedCategory As String = "^\[.\]$"
Const Ptn_FoodGroupNum As String = "^([０-９]|[0-9]{2})$"
Const Ptn_Jpn_Eng_Mix As String = "^[^A-Za-z0-9]+(\([^A-Za-z0-9]+\))?([A-Za-z])"
Const Ptn_JapaneseOnly As String = "^[^A-Za-z0-9]+(\([^A-Za-z0-9]+\))?$"
Const Ptn_Upper_Lower As String = "[A-Z][a-z]+"
Const Ptn_Upper_Only As String = "[A-Z]+"
Const Ptn_Lower_Only As String = "^[a-z]+$"
Const Ptn_RoundStart As String = "^[\(（]"
Const Ptn_Round_Exit As String = "[\(（][^A-Za-z0-9]+[\)）]"
Const Ptn_SquareStart As String = "^\["
Const Ptn_Square_Exit As String = "\[[^A-Za-z0-9]+\]"
Const Ptn_AngleStart As String = "^[\＞]"
Set mySht = Sh
Set myRng = mySht.UsedRange
tmpAr = myRng
Set RegExp_3_Digit_Num = CreateObject("VBScript.RegExp")
Set RegExp_Item_Number = CreateObject("VBScript.RegExp")
Set RegExp_SentakuHanni = CreateObject("VBScript.RegExp")
Set RegExp_SubCategory1 = CreateObject("VBScript.RegExp")
Set RegExp_SubCategory2 = CreateObject("VBScript.RegExp")
Set RegExp_MedCategory = CreateObject("VBScript.RegExp")
Set RegExp_Foods_Group = CreateObject("VBScript.RegExp")
Set RegExp_Jpn_Eng_Mix = CreateObject("VBScript.RegExp")
Set RegExp_JapaneseOnly = CreateObject("VBScript.RegExp")
Set RegExp_Upper_Lower = CreateObject("VBScript.RegExp")
Set RegExp_Upper_Only = CreateObject("VBScript.RegExp")
Set RegExp_Lower_Only = CreateObject("VBScript.RegExp")
Set RegExp_RoundBracket = CreateObject("VBScript.RegExp")
Set RegExp_SquareBracket = CreateObject("VBScript.RegExp")
Set RegExp_AngleBracket = CreateObject("VBScript.RegExp")
With RegExp_3_Digit_Num
.Pattern = "[0-9]{3}$"
.IgnoreCase = True
.Global = True
End With
With RegExp_Item_Number
.Pattern = "^[0-9]{5}$"
.IgnoreCase = True
.Global = True
End With
With RegExp_SentakuHanni
.Pattern = "(，|～)"
.IgnoreCase = True
.Global = True
End With
With RegExp_SubCategory1
.Pattern = "^(（|\().(）|\))$"
.IgnoreCase = True
.Global = True
End With
With RegExp_SubCategory2
.Pattern = "^(＜|＞)$"
.IgnoreCase = True
.Global = True
End With
With RegExp_MedCategory
.Pattern = "^\[.\]$"
.IgnoreCase = True
.Global = True
End With
With RegExp_Foods_Group
.Pattern = "^([０-９]|[0-9]{2})$"
.IgnoreCase = True
.Global = True
End With
With RegExp_Jpn_Eng_Mix
.Pattern = "^[^A-Za-z0-9]+(\([^A-Za-z0-9]+\))?([A-Za-z])"
.IgnoreCase = True
.Global = True
End With
With RegExp_JapaneseOnly
.Pattern = "^[^A-Za-z0-9]+(\([^A-Za-z0-9]+\))?$"
.IgnoreCase = True
.Global = True
End With
With RegExp_Upper_Lower
.Pattern = "[A-Z][a-z]+"
.IgnoreCase = False
.Global = True
End With
With RegExp_Upper_Only
.Pattern = "[A-Z]+"
.IgnoreCase = False
.Global = True
End With
With RegExp_Lower_Only
.Pattern = "^[a-z]+$"
.IgnoreCase = False
.Global = True
End With
j = 0
For i = LBound(tmpAr) + 1 To UBound(tmpAr)
With RegExp_RoundBracket
.Pattern = Ptn_RoundStart
.IgnoreCase = True
.Global = True
End With
With RegExp_SquareBracket
.Pattern = Ptn_SquareStart
.IgnoreCase = True
.Global = True
End With
With RegExp_AngleBracket
.Pattern = Ptn_AngleStart
.IgnoreCase = True
.Global = True
End With
strFoodGroup = ""
strSubFoodGroup = ""
strSub_Category = ""
strMajor_Category = ""
ReDim Preserve StartNumber(j)
ReDim Preserve Exit_Number(j)
ReDim Preserve FoodGroupJP(j)
ReDim Preserve FoodGroupEN(j)
ReDim Preserve Sub_FoodGroup_JP(j)
ReDim Preserve Sub_FoodGroup_EN(j)
ReDim Preserve Sub_Category_JPN(j)
ReDim Preserve Sub_Category_ENG(j)
ReDim Preserve Major_CategoryJP(j)
ReDim Preserve Major_CategoryEN(j)
ReDim Preserve Major_CategoryLT(j)
If RegExp_3_Digit_Num.Test(tmpAr(i, 1)) Then
Select Case True
Case RegExp_Item_Number.Test(tmpAr(i, 1))
StartNumber(j) = tmpAr(i, 1)
Exit_Number(j) = tmpAr(i, 1)
Case RegExp_SentakuHanni.Test(tmpAr(i, 1))
StartEnd = StartExit(tmpAr(i, 1))
StartNumber(j) = StartEnd(0)
Exit_Number(j) = StartEnd(1)
Erase StartEnd
End Select
FoodGroupJP(j) = strFoodGroupJP
FoodGroupEN(j) = strFoodGroupEN
If (i >= 19 And i = 370 And i = 599 And i = 635 And i = 646 And i ")
Case RegExp_RoundBracket.Test(tmpAr(i, 1))
For k = 1 To 8
strSub_Category = strSub_Category & " " & tmpAr(i, k)
Next k
strSub_Category = Trim(strSub_Category)
With RegExp_RoundBracket
.Pattern = Ptn_Round_Exit
.IgnoreCase = True
.Global = True
End With
Set myMatches = RegExp_RoundBracket.Execute(strSub_Category)
On Error Resume Next
strSub_CategoryJP = myMatches.Item(0).Value
strSub_CategoryJP = Replace(strSub_CategoryJP, "(", "（")
strSub_CategoryJP = Replace(strSub_CategoryJP, ")", "）")
strSub_CategoryEN = Mid(strSub_Category, myMatches.Item(0).Length + 2)
strSub_CategoryEN = Replace(strSub_CategoryEN, "（", "(")
strSub_CategoryEN = Replace(strSub_CategoryEN, "）", ")")
On Error GoTo 0
Case Else
End Select
j = j - 1
End If
j = j + 1
Next i
ReDim myArray(UBound(StartNumber), 10)
For n = LBound(myArray) To UBound(myArray)
myArray(n, 0) = StartNumber(n)
myArray(n, 1) = Exit_Number(n)
myArray(n, 2) = FoodGroupJP(n)
myArray(n, 3) = FoodGroupEN(n)
myArray(n, 4) = Sub_FoodGroup_JP(n)
myArray(n, 5) = Sub_FoodGroup_EN(n)
myArray(n, 6) = Sub_Category_JPN(n)
myArray(n, 7) = Sub_Category_ENG(n)
myArray(n, 8) = Major_CategoryJP(n)
myArray(n, 9) = Major_CategoryEN(n)
myArray(n, 10) = Major_CategoryLT(n)
Next n
MajorCategoryAr = myArray
End Function
Function StartExit(ByVal InputStr As String) As String()
Dim str As String
Dim Ar() As String
str = InputStr
ReDim Ar(1)
Ar(0) = Left(str, 5)
Ar(1) = Left(str, 2) & Right(str, 3)
StartExit = Ar
End Function
Function Separate_Jpn_Eng(ByVal InputStr As String) As String()
Dim str As String
Dim Ar() As String
Dim RegExp_Jpn_Eng_Mix As Object
Dim myMatches As Object
Dim myMatch As Object
Const Ptn_Jpn_Eng_Mix As String = "^[^A-Za-z0-9]+(\([^A-Za-z0-9]+\))?([A-Za-z])"
str = InputStr
ReDim Ar(1)
Set RegExp_Jpn_Eng_Mix = CreateObject("VBScript.RegExp")
With RegExp_Jpn_Eng_Mix
.Pattern = Ptn_Jpn_Eng_Mix
.IgnoreCase = True
.Global = True
End With
Set myMatches = RegExp_Jpn_Eng_Mix.Execute(str)
For Each myMatch In myMatches
If myMatches.Count > 0 Then
Ar(0) = Left(str, myMatches.Item(0).Length - 1)
Ar(1) = Mid(str, myMatches.Item(0).Length)
End If
Next myMatch
Separate_Jpn_Eng = Ar
End Function
Sub Separate_by_Parent()
Dim mySht1 As Worksheet
Dim mySht2 As Worksheet
Dim mySht3 As Worksheet
Dim myRng As Range
Dim tmpAr As Variant
Dim Major_CategoryAr As Variant
Dim No_Cancel_Ar As Variant
Dim ItemNamAr() As String
Dim ItemNumAr() As String
Dim JapaneseName() As String
Dim English_Name() As String
Dim ItemArray() As String
Dim Residual_JPN() As String
Dim Residual_ENG() As String
Dim Residual_Row() As String
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim n As Long
Dim p As Long
Dim q As Long
Dim r As Long
Dim s As Long
Dim t As Long
Dim str_JPN_Analyse As String
Dim str_ENG_Analyse As String
Dim strFoodGroup As String
Dim strFoodGroupJP As String
Dim strFoodGroupEN As String
Dim strSubFoodGroup As String
Dim strSubFoodGroupJP As String
Dim strSubFoodGroupEN As String
Dim strSub_Category As String
Dim strSub_CategoryJP As String
Dim strSub_CategoryEN As String
Dim strMajor_Category As String
Dim strMajor_CategoryJP As String
Dim strMajor_CategoryEN As String
Dim strMediumCategory As String
Dim strMediumCategoryJP As String
Dim strMediumCategoryEN As String
Dim strMinor_Category As String
Dim strMinor_CategoryJP As String
Dim strMinor_CategoryEN As String
Dim strDetailCategory As String
Dim strDetailCategoryJP As String
Dim strDetailCategoryEN As String
Dim FoodGrouNum() As String
Dim FoodGroupJP() As String
Dim FoodGroupEN() As String
Dim Sub_FoodGroup_JP() As String
Dim Sub_FoodGroup_EN() As String
Dim Sub_Group_JP_Row() As String
Dim Sub_Group_EN_Row() As String
Dim Sub_Category_JPN() As String
Dim Sub_Category_ENG() As String
Dim SubCategory_RowJ() As String
Dim SubCategory_RowE() As String
Dim Major_CategoryJP() As String
Dim Major_CategoryEN() As String
Dim Major_CategoryLT() As String
Dim Major_JPN_RowNum() As String
Dim Major_ENG_RowNum() As String
Dim Major_Temp_Array() As String
Dim MediumCategoryJP() As String
Dim MediumCategoryEN() As String
Dim Med_JP_RowNumber() As Long
Dim Med_EN_RowNumber() As Long
Dim Med_Category_JPN() As String
Dim Med_Category_ENG() As String
Dim MediumCategoryAr() As String
Dim Minor_CategoryJP() As String
Dim Minor_CategoryEN() As String
Dim Min_JP_RowNumber() As Long
Dim Min_EN_RowNumber() As Long
Dim Min_Category_JPN() As String
Dim Min_Category_ENG() As String
Dim Minor_CategoryAr() As String
Dim DetailCategoryJP() As String
Dim DetailCategoryEN() As String
Const Ptn_FoodGroupNum As String = "^([０-９]|[0-9]{2})$"
Const Ptn_Jpn_Eng_Mix As String = "^[^A-Za-z0-9]+(\([^A-Za-z0-9]+\))?([A-Za-z])"
Const Ptn_JapaneseOnly As String = "^[^A-Za-z0-9\*]+(\([^A-Za-z0-9]+\))?$"
Const Ptn_Upper_Lower As String = "[A-Za-z\s:\-,]+" '"[A-Za-z,\s]+"
Const Ptn_Upper_Only As String = "[A-Z]+"
Const Ptn_Lower_Only As String = "^[a-z]+$"
Const Ptn_AngleStart As String = "^[\＞]"
Const Ptn_Angle_ENG As String = "[＞]"
Const Ptn_RoundStart As String = "^[\(（][^0-9]+"
Const Ptn_Round_JPN As String = "[\(（][^A-Za-z0-9]+[\)）]"
Const Ptn_Round_ENG As String = "[\(（][A-Za-z\s]+[\)）]"
Const Ptn_SquareStart As String = "^\["
Const Ptn_Square_JPN As String = "\[[^A-Za-z0-9]+\]"
Const Ptn_Square_ENG As String = "\[[A-Za-z\s:\-,]+(\]|］)"
Dim RegExp_MedCategory As Object
Dim RegExp_Foods_Group As Object
Dim RegExp_Jpn_Eng_Mix As Object
Dim RegExp_JapaneseOnly As Object
Dim RegExp_English_Only As Object
Dim RegExp_Upper_Lower As Object
Dim RegExp_Upper_Only As Object
Dim RegExp_Lower_Only As Object
Dim RegExp_Angle_Bracket As Object
Dim RegExp_Angle_Bracket_JP As Object
Dim RegExp_Angle_Bracket_EN As Object
Dim RegExp_Round_Bracket As Object
Dim RegExp_Round_Bracket_JP As Object
Dim RegExp_Round_Bracket_EN As Object
Dim RegExp_SquareBracket As Object
Dim RegExp_SquareBracket_JP As Object
Dim RegExp_SquareBracket_EN As Object
Dim RegExp_5_Number As Object
Dim RegExp_Japanese As Object
Dim RegExp_Alphabet As Object
Dim myMatches As Object
Dim myMatch As Object
Const Ptn_5_Number As String = "^[0-9]{5}$"
Const Ptn_Japanese As String = "[^A-Za-z0-9]{2,}"
Const Ptn_Alphabet As String = "^[A-Za-z]{2,}"
Dim CEREALS As Long
Dim POTATOES As Long
Dim SUGARS As Long
Dim PULSES As Long
Dim NUTS As Long
Dim VEGETABLES As Long
Dim FRUITS As Long
Dim MUSHROOMS As Long
Dim ALGAE As Long
Dim FISHES As Long
Dim MEATS As Long
Dim EGGS As Long
Dim MILK As Long
Dim OIL As Long
Dim CONFECTIONERIES As Long
Dim BEVERAGES As Long
Dim SEASONINGS As Long
Dim PREPARED As Long
Dim RegExpJapaneseName As Object
Const Ptn_JapaneseName As String = "^([0-9%]{1,3})?[^A-Za-z0-9]+"
Set RegExpJapaneseName = CreateObject("VBScript.RegExp")
With RegExpJapaneseName
.Pattern = Ptn_JapaneseName
.IgnoreCase = True
.Global = True
End With
Dim RegExp_EnglishName As Object
Dim Ptn_EnglishName As String
Ptn_EnglishName = "^[A-Za-z0-9%\.,\-'" & ChrW(&HC0) & "-" & ChrW(&HFF) & "]+$"
Set RegExp_EnglishName = CreateObject("VBScript.RegExp")
With RegExp_EnglishName
.Pattern = Ptn_EnglishName
.IgnoreCase = True
.Global = True
End With
Set RegExp_5_Number = CreateObject("VBScript.RegExp")
Set RegExp_MedCategory = CreateObject("VBScript.RegExp")
Set RegExp_Foods_Group = CreateObject("VBScript.RegExp")
Set RegExp_Jpn_Eng_Mix = CreateObject("VBScript.RegExp")
Set RegExp_JapaneseOnly = CreateObject("VBScript.RegExp")
Set RegExp_English_Only = CreateObject("VBScript.RegExp")
Set RegExp_Upper_Lower = CreateObject("VBScript.RegExp")
Set RegExp_Upper_Only = CreateObject("VBScript.RegExp")
Set RegExp_Lower_Only = CreateObject("VBScript.RegExp")
Set RegExp_Angle_Bracket = CreateObject("VBScript.RegExp")
Set RegExp_Angle_Bracket_JP = CreateObject("VBScript.RegExp")
Set RegExp_Angle_Bracket_EN = CreateObject("VBScript.RegExp")
Set RegExp_Round_Bracket = CreateObject("VBScript.RegExp")
Set RegExp_Round_Bracket_JP = CreateObject("VBScript.RegExp")
Set RegExp_Round_Bracket_EN = CreateObject("VBScript.RegExp")
Set RegExp_SquareBracket = CreateObject("VBScript.RegExp")
Set RegExp_SquareBracket_JP = CreateObject("VBScript.RegExp")
Set RegExp_SquareBracket_EN = CreateObject("VBScript.RegExp")
Set RegExp_Japanese = CreateObject("VBScript.RegExp")
Set RegExp_Alphabet = CreateObject("VBScript.RegExp")
With RegExp_5_Number
.Pattern = Ptn_5_Number
.IgnoreCase = True
.Global = True
End With
With RegExp_Angle_Bracket
.Pattern = Ptn_AngleStart
.IgnoreCase = True
.Global = True
End With
With RegExp_Angle_Bracket_JP
.Pattern = Ptn_Angle_JPN
.IgnoreCase = True
.Global = True
End With
With RegExp_Angle_Bracket_EN
.Pattern = Ptn_Angle_ENG
.IgnoreCase = True
.Global = True
End With
With RegExp_Round_Bracket
.Pattern = Ptn_RoundStart
.IgnoreCase = True
.Global = True
End With
With RegExp_Round_Bracket_JP
.Pattern = Ptn_Round_JPN
.IgnoreCase = True
.Global = True
End With
With RegExp_Round_Bracket_EN
.Pattern = Ptn_Round_ENG
.IgnoreCase = True
.Global = True
End With
With RegExp_SquareBracket
.Pattern = Ptn_SquareStart
.IgnoreCase = True
.Global = True
End With
With RegExp_SquareBracket_JP
.Pattern = Ptn_Square_JPN
.IgnoreCase = True
.Global = True
End With
With RegExp_SquareBracket_EN
.Pattern = Ptn_Square_ENG
.IgnoreCase = True
.Global = True
End With
With RegExp_Japanese
.Pattern = Ptn_Japanese
.IgnoreCase = True
.Global = True
End With
With RegExp_Alphabet
.Pattern = Ptn_Alphabet
.IgnoreCase = False
.Global = True
End With
With RegExp_JapaneseOnly
.Pattern = Ptn_JapaneseOnly
.IgnoreCase = True
.Global = True
End With
Set mySht1 = Worksheets("Sheet1")
Set mySht2 = Worksheets("Sheet2")
Set myRng = mySht1.UsedRange
tmpAr = myRng
Major_CategoryAr = MajorCategoryAr(mySht2)
ReDim Preserve Major_CategoryAr(UBound(Major_CategoryAr), UBound(Major_CategoryAr, 2) + 2)
m = 0
For i = LBound(tmpAr) To UBound(tmpAr)
For n = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)
If Major_CategoryAr(n, 0) = tmpAr(i, 1) Then
Major_CategoryAr(n, 11) = i
End If
If Major_CategoryAr(n, 1) = tmpAr(i, 1) Then
Major_CategoryAr(n, 12) = i
End If
Next n
Next i
m = 0
n = 0
p = 0
q = 0
No_Cancel_Ar = NoCancelArray(mySht1)
For r = LBound(No_Cancel_Ar) To UBound(No_Cancel_Ar)
For i = No_Cancel_Ar(r, 0) To No_Cancel_Ar(r, 1)
str_JPN_Analyse = ""
str_ENG_Analyse = ""
On Error Resume Next
For k = 1 To 5
str_JPN_Analyse = str_JPN_Analyse & tmpAr(i, k)
str_ENG_Analyse = str_ENG_Analyse & " " & tmpAr(i + 1, k)
str_ENG_Analyse = Replace(str_ENG_Analyse, " ", " ")
Next k
For k = 1 To 3
str_ENG_Analyse = str_ENG_Analyse & " " & tmpAr(i + 2, k)
str_ENG_Analyse = Replace(str_ENG_Analyse, " ", " ")
Next k
On Error GoTo 0
str_ENG_Analyse = Trim(str_ENG_Analyse)
Select Case True
Case RegExp_Angle_Bracket.Test(str_JPN_Analyse) And _
RegExp_Angle_Bracket.Test(str_ENG_Analyse)
ReDim Preserve Sub_FoodGroup_JP(p)
ReDim Preserve Sub_FoodGroup_EN(p)
ReDim Preserve Sub_Group_JP_Row(p)
ReDim Preserve Sub_Group_EN_Row(p)
Set myMatches = RegExp_Angle_Bracket_JP.Execute(str_JPN_Analyse)
Sub_FoodGroup_JP(p) = myMatches.Item(0).Value
Sub_Group_JP_Row(p) = i
Set myMatches = RegExp_Angle_Bracket_EN.Execute(str_ENG_Analyse)
Sub_FoodGroup_EN(p) = myMatches.Item(0).Value
Sub_FoodGroup_EN(p) = Replace(Sub_FoodGroup_EN(p), "＜", "")
Sub_Group_EN_Row(p) = i + 1
p = p + 1
Case RegExp_Round_Bracket_JP.Test(str_JPN_Analyse) And _
RegExp_Round_Bracket_EN.Test(str_ENG_Analyse)
ReDim Preserve Sub_Category_JPN(n)
ReDim Preserve Sub_Category_ENG(n)
ReDim Preserve SubCategory_RowJ(n)
ReDim Preserve SubCategory_RowE(n)
Set myMatches = RegExp_Round_Bracket_JP.Execute(str_JPN_Analyse)
Sub_Category_JPN(n) = myMatches.Item(0).Value
Sub_Category_JPN(n) = Replace(Sub_Category_JPN(n), "(", "（")
Sub_Category_JPN(n) = Replace(Sub_Category_JPN(n), ")", "）")
SubCategory_RowJ(n) = i
Set myMatches = RegExp_Round_Bracket_EN.Execute(str_ENG_Analyse)
Sub_Category_ENG(n) = myMatches.Item(0).Value
Sub_Category_ENG(n) = Replace(Sub_Category_ENG(n), "（", "(")
Sub_Category_ENG(n) = Replace(Sub_Category_ENG(n), "）", ")")
SubCategory_RowE(n) = i + 1
n = n + 1
Case RegExp_SquareBracket_JP.Test(str_JPN_Analyse) And _
RegExp_SquareBracket_EN.Test(str_ENG_Analyse)
ReDim Preserve MediumCategoryJP(m)
ReDim Preserve Med_JP_RowNumber(m)
ReDim Preserve MediumCategoryEN(m)
ReDim Preserve Med_EN_RowNumber(m)
Set myMatches = RegExp_SquareBracket_JP.Execute(str_JPN_Analyse)
MediumCategoryJP(m) = myMatches.Item(0).Value
Med_JP_RowNumber(m) = i
Set myMatches = RegExp_SquareBracket_EN.Execute(str_ENG_Analyse)
MediumCategoryEN(m) = myMatches.Item(0).Value
Med_EN_RowNumber(m) = i + 1
m = m + 1
Case RegExp_Japanese.Test(str_JPN_Analyse) And _
RegExp_Alphabet.Test(str_ENG_Analyse)
ReDim Preserve Major_CategoryJP(q)
ReDim Preserve Major_CategoryEN(q)
ReDim Preserve Major_JPN_RowNum(q)
ReDim Preserve Major_ENG_RowNum(q)
Set myMatches = RegExp_Japanese.Execute(str_JPN_Analyse)
Major_CategoryJP(q) = myMatches.Item(0).Value
Major_JPN_RowNum(q) = i
Set myMatches = RegExp_Alphabet.Execute(str_ENG_Analyse)
Major_CategoryEN(q) = myMatches.Item(0).Value
Major_ENG_RowNum(q) = i + 1
q = q + 1
Case Else
End Select
Next i
Next r
ReDim Major_Temp_Array(UBound(Major_CategoryJP), 5)
For q = LBound(Major_Temp_Array) To UBound(Major_Temp_Array) - 1
Major_Temp_Array(q, 0) = Major_CategoryJP(q)
Major_Temp_Array(q, 1) = Major_JPN_RowNum(q)
Major_Temp_Array(q, 2) = Major_JPN_RowNum(q + 1)
Major_Temp_Array(q, 3) = Major_CategoryEN(q)
Major_Temp_Array(q, 4) = Major_ENG_RowNum(q)
Major_Temp_Array(q, 5) = Major_ENG_RowNum(q + 1)
Next q
Major_Temp_Array(q, 0) = Major_CategoryJP(UBound(Major_Temp_Array))
Major_Temp_Array(q, 1) = Major_JPN_RowNum(UBound(Major_Temp_Array))
Major_Temp_Array(q, 2) = 32757
Major_Temp_Array(q, 3) = Major_CategoryEN(UBound(Major_Temp_Array))
Major_Temp_Array(q, 4) = Major_ENG_RowNum(UBound(Major_Temp_Array))
Major_Temp_Array(q, 5) = 32757
ReDim MediumCategoryAr(UBound(MediumCategoryJP), 5)
For m = LBound(MediumCategoryAr) To UBound(MediumCategoryAr) - 1
MediumCategoryAr(m, 0) = MediumCategoryJP(m)
MediumCategoryAr(m, 1) = Med_JP_RowNumber(m)
MediumCategoryAr(m, 2) = Med_JP_RowNumber(m + 1)
MediumCategoryAr(m, 3) = MediumCategoryEN(m)
MediumCategoryAr(m, 4) = Med_EN_RowNumber(m)
MediumCategoryAr(m, 5) = Med_EN_RowNumber(m + 1)
Next m
MediumCategoryAr(m, 0) = MediumCategoryJP(UBound(MediumCategoryAr))
MediumCategoryAr(m, 1) = Med_JP_RowNumber(UBound(MediumCategoryAr))
MediumCategoryAr(m, 2) = 26271
MediumCategoryAr(m, 3) = MediumCategoryEN(UBound(MediumCategoryAr))
MediumCategoryAr(m, 4) = Med_EN_RowNumber(UBound(MediumCategoryAr))
MediumCategoryAr(m, 5) = 26271
For m = LBound(MediumCategoryAr) To UBound(MediumCategoryAr)
For n = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)
If CLng(MediumCategoryAr(m, 1)) > CLng(Major_CategoryAr(n, 11)) And _
CLng(MediumCategoryAr(m, 1)) CLng(Major_CategoryAr(n, 11)) And _
CLng(MediumCategoryAr(m, 4)) 0 And _
InStr(tmpAr(i + 1, 1), Major_CategoryAr(n, 9)) 0 And _
i >= Major_CategoryAr(n, 11) And _
i "（欠番）" Then
ReDim Preserve ItemNamAr(j)
ReDim Preserve ItemNumAr(j)
ReDim Preserve FoodGrouNum(j)
ReDim Preserve FoodGroupJP(j)
ReDim Preserve FoodGroupEN(j)
ReDim Preserve Sub_FoodGroup_JP(j)
ReDim Preserve Sub_FoodGroup_EN(j)
ReDim Preserve Sub_Category_JPN(j)
ReDim Preserve Sub_Category_ENG(j)
ReDim Preserve Major_CategoryJP(j)
ReDim Preserve Major_CategoryEN(j)
ReDim Preserve Major_CategoryLT(j)
ReDim Preserve Med_Category_JPN(j)
ReDim Preserve Med_Category_ENG(j)
ReDim Preserve Minor_CategoryJP(j)
ReDim Preserve Minor_CategoryEN(j)
ReDim Preserve DetailCategoryJP(j)
ReDim Preserve DetailCategoryEN(j)
ReDim Preserve JapaneseName(j)
ReDim Preserve English_Name(j)
ItemNamAr(j) = tmpAr(i, 1)
ItemNumAr(j) = i
Select Case True
Case Left(tmpAr(i, 1), 2) = "01"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "穀類"
FoodGroupEN(j) = "CEREALS"
CEREALS = CEREALS + 1
Case Left(tmpAr(i, 1), 2) = "02"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "いも及びでん粉類"
FoodGroupEN(j) = "POTATOES AND STARCHES"
POTATOES = POTATOES + 1
Case Left(tmpAr(i, 1), 2) = "03"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "砂糖及び甘味類"
FoodGroupEN(j) = "SUGARS"
SUGARS = SUGARS + 1
Case Left(tmpAr(i, 1), 2) = "04"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "豆類"
FoodGroupEN(j) = "PULSES"
PULSES = PULSES + 1
Case Left(tmpAr(i, 1), 2) = "05"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "種実類"
FoodGroupEN(j) = "NUTS AND SEEDS"
NUTS = NUTS + 1
Case Left(tmpAr(i, 1), 2) = "06"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "野菜類"
FoodGroupEN(j) = "VEGETABLES"
VEGETABLES = VEGETABLES + 1
Case Left(tmpAr(i, 1), 2) = "07"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "果実類"
FoodGroupEN(j) = "FRUITS"
FRUITS = FRUITS + 1
Case Left(tmpAr(i, 1), 2) = "08"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "きのこ類"
FoodGroupEN(j) = "MUSHROOMS"
MUSHROOMS = MUSHROOMS + 1
Case Left(tmpAr(i, 1), 2) = "09"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "藻類"
FoodGroupEN(j) = "ALGAE"
ALGAE = ALGAE + 1
Case Left(tmpAr(i, 1), 2) = "10"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "魚介類"
FoodGroupEN(j) = "FISHES AND SHELLFISHES"
FISHES = FISHES + 1
Case Left(tmpAr(i, 1), 2) = "11"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "肉類"
FoodGroupEN(j) = "MEATS"
MEATS = MEATS + 1
Case Left(tmpAr(i, 1), 2) = "12"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "卵類"
FoodGroupEN(j) = "EGGS"
EGGS = EGGS + 1
Case Left(tmpAr(i, 1), 2) = "13"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "乳類"
FoodGroupEN(j) = "MILKS"
MILK = MILK + 1
Case Left(tmpAr(i, 1), 2) = "14"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "油脂類"
FoodGroupEN(j) = "FATS AND OILS"
OIL = OIL + 1
Case Left(tmpAr(i, 1), 2) = "15"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "菓子類"
FoodGroupEN(j) = "CONFECTIONERIES"
CONFECTIONERIES = CONFECTIONERIES + 1
Case Left(tmpAr(i, 1), 2) = "16"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "し好飲料類"
FoodGroupEN(j) = "BEVERAGES"
BEVERAGES = BEVERAGES + 1
Case Left(tmpAr(i, 1), 2) = "17"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "調味料及び香辛料類"
FoodGroupEN(j) = "SEASONINGS AND SPICES"
SEASONINGS = SEASONINGS + 1
Case Left(tmpAr(i, 1), 2) = "18"
FoodGrouNum(j) = Left(tmpAr(i, 1), 2)
FoodGroupJP(j) = "調理加工食品類"
FoodGroupEN(j) = "PREPARED FOODS"
PREPARED = PREPARED + 1
Case Else
End Select
If RegExpJapaneseName.Test(tmpAr(i, 2)) Then
Set myMatches = RegExpJapaneseName.Execute(tmpAr(i, 2))
JapaneseName(j) = myMatches.Item(0).Value
End If
For t = 1 To 6
If RegExp_EnglishName.Test(tmpAr(i + 1, t)) Then
English_Name(j) = English_Name(j) & " " & tmpAr(i + 1, t)
English_Name(j) = Trim(English_Name(j))
Else
Exit For
End If
Next t
For k = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)
If CLng(tmpAr(i, 1)) >= CLng(Major_CategoryAr(k, 0)) _
And CLng(tmpAr(i, 1)) = CLng(MediumCategoryAr(m, 1)) And _
i = CLng(MediumCategoryAr(m, 4)) And _
i "1)" Then
j = j + 1
ReDim Preserve CancelRow1(j)
ReDim Preserve CancelRow2(j)
CancelRow1(j) = myCancelAr(i, 1)
CancelRow2(j) = myCancelAr(i, 2)
End If
Next i
ReDim Cancel_Array(UBound(CancelRow1), 1)
j = 0
For j = LBound(Cancel_Array) To UBound(Cancel_Array)
Cancel_Array(j, 0) = CancelRow1(j)
Cancel_Array(j, 1) = CancelRow2(j)
Next j
j = 0
Cancel_Array(j, 0) = 1
Cancel_Array(j, 1) = CancelRow1(j)
For j = LBound(Cancel_Array) + 1 To UBound(Cancel_Array)
Cancel_Array(j, 0) = CancelRow2(j - 1)
Cancel_Array(j, 1) = CancelRow1(j)
Next j
NoCancelArray = Cancel_Array
End Function
References:
CSV file of the ‘Standard Tables of Food Composition in Japan 2010′
Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 2
