[已解决]txt提取多个指定字段

tcmsod · 发表于 2017-7-19 15:05

本帖最后由 tcmsod 于 2017-7-19 21:48 编辑

需提取txt中 Purchase?order?:?和Customer?Po: 后的字符，以及数字1开头后字符到excel，详见以下描述及附件说明，请高手抽空解答，谢谢。

期待结果(将符合结果分开三列写到excel表中A:C三列)

VV114937_3	PO99054659-1	ME73E0227940
RR242495_0	PO99027099-1	99360

实际运行结果(第1列结果没有提取成功，B:C二列提取结果错行)

	PO99054659-1
		ME73E0227940
	PO99027099-1
		99360

代码：

Sub test()
Dim ss As String, a%
Open "E:\input\12\po2.txt" For Input As #1
Do While Not EOF(1)
      Input #1, ss
      ss = Application.Trim(ss)
      If ss Like "Purchase?order?:?" & "*" Or ss Like "Customer?Po: " & "*" Or ss Like "1 " & "*" & "USD" Then
         a = a + 1
         If ss Like "Purchase?order?:?" & "*" Then
            Cells(a, 2) = Replace(ss, "Purchase?order?:?", "")
         ElseIf ss Like "Customer?Po: " & "*" Then
            Cells(a, 3) = Replace(ss, "Customer?Po: ", "")
         Else
            Cells(a, 1) = Split(ss, " ")(0)
         End If
      End If
Loop
Close #1
End Sub

最佳答案

月排行榜 / 总排行榜

大灰狼1976

2017-7-19 17:05

Sub test()
Dim ss As String, a%, b&
Open ThisWorkbook.Path & "\po2.txt" For Input As #1
a = 1
Do While Not EOF(1)
Input #1, ss
ss = Application.Trim(ss)
If ss Like "Purchase?order?:?" & "*" Then
Cells(a, 2) = Replace(ss, "Purchase?order?:?", "")
b = b + 1
ElseIf ss Like "Customer?Po: " & "*" Then
Cells(a, 3) = Replace(ss, "Customer?Po: ", "")
b = b + 1
ElseIf ss Like "1 " & "*" & "USD" & "*" Then
Cells(a, 1) = Split(ss, " ")(1)
b = b + 1
End If
If b = 3 Then a = a + 1: b = 0
Loop
Close #1
End Sub

复制代码

跳转到最佳答案楼层

大灰狼1976 · 发表于 2017-7-19 17:05

这个最佳答案由大灰狼1976 给出，感谢大灰狼1976 的回答。

单击隐藏图章

Sub test()
Dim ss As String, a%, b&
Open ThisWorkbook.Path & "\po2.txt" For Input As #1
a = 1
Do While Not EOF(1)
Input #1, ss
ss = Application.Trim(ss)
If ss Like "Purchase?order?:?" & "*" Then
Cells(a, 2) = Replace(ss, "Purchase?order?:?", "")
b = b + 1
ElseIf ss Like "Customer?Po: " & "*" Then
Cells(a, 3) = Replace(ss, "Customer?Po: ", "")
b = b + 1
ElseIf ss Like "1 " & "*" & "USD" & "*" Then
Cells(a, 1) = Split(ss, " ")(1)
b = b + 1
End If
If b = 3 Then a = a + 1: b = 0
Loop
Close #1
End Sub

复制代码

砂海 · 发表于 2017-7-19 17:18

Sub test()
Dim ss As String, a%, i%
Open ThisWorkbook.Path & "\po2.txt" For Input As #1
Do While Not EOF(1)
      Input #1, ss
      ss = Application.Trim(ss)

      Select Case i

         Case 0
            If ss Like "Purchase?order?:?" & "*" Then
                  a = a + 1
                  Cells(a, 2) = Replace(ss, "Purchase?order?:?", "")
                  i = 1
            End If

         Case 1
            If ss Like "Customer?Po: " & "*" Then
                  Cells(a, 3) = Replace(ss, "Customer?Po: ", "")
                  i = 2
            End If

         Case 2
            If ss Like "1 " & "*" Then
                  i = 0
                  Cells(a, 1) = Split(ss, " ")(1)
            End If

      End Select
Loop
Close #1
End Sub

砂海 · 发表于 2017-7-19 18:13

If ss Like "Purchase[?]order[?]:[?]*"
这样更精准 , 否则
Purchase■order■:● like也是 True

当然 , 此处没有问题.

tcmsod · 发表于 2017-7-19 21:50

谢谢两位大侠，测试都可以运行成功，只好按先后顺序采纳最佳

		自动登录	找回密码
密码			注册