VBA - 提取String的特定部分

I am pretty new to VBA, but not programming in general. I am trying to make a macro in VBA that formats the model name of a list of phone names to something manageable. E.g. I want the following name:

我对VBA很新,但不是一般的编程。我正在尝试在VBA中创建一个宏,将一组电话名称的模型名称格式化为可管理的名称。例如。我想要以下名字:

P10, 12,9 cm (5.1"), 4 GB, 64 GB, 20 MP

P10,12.9厘米(5.1“),4 GB,64 GB,20 MP

To be formatted as

格式化为

P10 5.1" 64 GB

P10 5.1“64 GB

I have succeeded in extracting the first two elements of the formatted name using Split and Mid.

我已成功使用Split和Mid提取格式化名称的前两个元素。

This is some of my code for the first two parts:

这是前两部分的一些代码:

'First part of format
firstPart = Trim(Split(modelName, ",")(0))
Debug.Print formattedModelName

'Second part of format
openPos = InStr(modelName, "(")
closePos = InStr(modelName, ")")

secondPart = Mid(modelName, openPos + 1, closePos - openPos - 1)

My question is how to go about extracting the 64 GB and not the 4 GB. I tried iterating through all characters and saving the last 5 characters to see if they matched what I wanted. But there must be a better way.

我的问题是如何提取64 GB而不是4 GB。我尝试迭代所有字符并保存最后5个字符,看看它们是否与我想要的匹配。但必须有更好的方法。

I would appreciate any help, Thank you very much

我将不胜感激,非常感谢

EDIT

Here is another example of an input and the output as requested: Input:

这是输入的另一个示例和请求的输出:输入:

iPhone iPhone SE, 10,2 cm (4"), 640 x 1136 pixel, 16 GB, 12 MP, iOS 9, Sort, Grå

iPhone iPhone SE,10,2厘米(4英寸),640 x 1136像素,16 GB,12 MP,iOS 9,排序,格拉

Expected Output:

iPhone iPhone SE 4" 16 GB

iPhone iPhone SE 4“16 GB

6 个解决方案

#1

How about using RegEx to extract the values and using the last one. Just add a reference to the library: "Microsoft VBScript Regular Expressions 5.5". Then you can do this..

如何使用RegEx提取值并使用最后一个值。只需添加对库的引用:“Microsoft VBScript Regular Expressions 5.5”。然后你可以这样做..

Option Explicit

Public Function xx()
Dim modelName As String
Dim firstPart As String
Dim Pattern As String
Dim GBString As String
Dim regEx As New RegExp
Dim finalName As String
Dim matches As MatchCollection

Let modelName = "P10, 12,9 cm (5.1""), 4 GB, 64 GB, 20 MP"
firstPart = Trim(Split(modelName, ",")(0))


With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "[0-9]+ GB"
End With

Set matches = regEx.Execute(modelName)

If matches.Count > 0 Then
    GBString = matches(matches.Count)
Else
    GBString = "<unknown GB>"
End If

finalName = firstPart + GBString

End Function

#2

Try,

Option Explicit

Function oneTwoThree(str As String)

    Dim vals As Variant

    vals = Split(str, ",")

    oneTwoThree = Join(Array(vals(0), _
                             Split(Split(vals(2), "(")(1), ")")(0), _
                             Trim(vals(4 + (InStr(1, vals(4), "gb", vbTextCompare) = 0)))), " ")

End Function

#3

Try the next segment of code:

尝试下一段代码:

Dim modelName As String: modelName = "P10, 12,9 cm (5.1""), 4 GB, 64 GB, 20 MP"
Dim data() As String: data = Split(modelName, ",")
Dim firstPart As String
Dim secondPart As String
Dim thirdPart As String

firstPart = Trim(data(0))
secondPart = Trim(Replace(Mid(data(2), InStr(data(2), "(") + 1), ")", ""))
thirdPart = Trim(data(4))


MsgBox firstPart & " " & secondPart & " " & thirdPart

You have to be very careful about the delimiter data substring. For example, if your main text is P10, 12 cm (5.1"), 4 GB, 64 GB, 20 MP the code doesn't work anymore. Assuming you always have a comma and a space between data elements you could write instead:

您必须非常小心分隔符数据子字符串。例如,如果您的主文本是P10,12厘米(5.1英寸),4 GB,64 GB,20 MP,则代码将不再起作用。假设您始终在数据元素之间使用逗号和空格,则可以编写:

Dim modelName As String: modelName = "P10, 12,9 cm (5.1""), 4 GB, 64 GB, 20 MP"
Dim data() As String: data = Split(modelName, ", ")
Dim firstPart As String
Dim secondPart As String
Dim thirdPart As String

firstPart = Trim(data(0))
secondPart = Trim(Replace(Mid(data(1), InStr(data(1), "(") + 1), ")", ""))
thirdPart = Trim(data(3))

MsgBox firstPart & " " & secondPart & " " & thirdPart

And it will always work.

它会永远有效。

If the element 4 MB is not always present do the following:

如果元素4 MB并不总是存在,请执行以下操作:

Dim modelName As String: modelName = "P10, 12,9 cm (5.1""), 4 GB, 64 GB, 20 MP"
Dim data() As String: data = Split(modelName, ", ")
Dim firstPart As String
Dim secondPart As String
Dim thirdPart As String

firstPart = Trim(data(0))
secondPart = Trim(Replace(Mid(data(1), InStr(data(1), "(") + 1), ")", ""))

Select Case InStr(modelName, "4 GB")
    Case 0
        thirdPart = Trim(data(2))
    Case Else
        thirdPart = Trim(data(3))
End Select

MsgBox firstPart & " " & secondPart & " " & thirdPart

UPDATE:

I think now I got what you want exactly. I tried the next code with the three examples you gave us on your question and it worked (the problem was I didn't understand that you wanted the last data value before MD value.

我想现在我得到了你想要的东西。我用你在问题上给我们的三个例子尝试了下一个代码并且它有效(问题是我不明白你想要MD值之前的最后一个数据值。

Dim modelName As String
Dim data() As String
Dim firstPart As String
Dim secondPart As String
Dim thirdPart As String

modelName = "P10, 12,9 cm (5.1""), 4 GB, 64 GB, 20 MP"
data = Split(modelName, ", ")
firstPart = Trim(data(0))
secondPart = Trim(Replace(Mid(data(1), InStr(data(1), "(") + 1), ")", ""))
thirdPart = Trim(data(Application.Match("*MP", data, 0) - 2))

MsgBox firstPart & " " & secondPart & " " & thirdPart

You could try with

你可以试试

modelName = "P10, 12,9 cm (5.1""), 64 GB, 20 MP"

or with

modelName = "iPhone iPhone SE, 10,2 cm (4""), 640 x 1136 pixel, 16 GB, 12 MP, iOS 9, Sort, Grå"

It will always give you the answer.

它总能给你答案。

#4

Here is a function I have made that should always work.

这是我所做的一个应该始终有效的功能。

For dealing with the issue of getting the correct storage value it goes through each section separated by commas and looks for "GB" and then it keeps track of the largest value in one of these sections, which must then be the storage value.

为了解决获取正确存储值的问题,它会通过逗号分隔的每个部分查找“GB”,然后跟踪其中一个部分中的最大值,然后必须是存储值。

Public Function FormatName(Inp As String) As String

    Dim ArrInp As Variant, i As Integer, Temp As String

    ArrInp = Split(Trim(Inp), ",")
    FormatName = ArrInp(0) & " " & Mid(Inp, InStr(Inp, "(") + 1, InStr(Inp, ")") - InStr(Inp, "(") - 1)

    For i = 1 To UBound(ArrInp)
        If InStr(UCase(ArrInp(i)), "GB") <> 0 And Val(ArrInp(i)) > a Then
            Temp = ArrInp(i)
        End If
    Next
    FormatName = FormatName & Temp

End Function

Hope this helps.

希望这可以帮助。

#5

I'm little late to the party !

我迟到了!

I was giving this a quick try with simple formula approach and was able to come up with something like this which seems to be working with the current set of sample data.

我用简单的公式方法快速尝试了这个,并且能够提出类似这样的东西,它似乎与当前的样本数据集一起使用。

If your text is in cell A1 then give these formulas a try for extracting the relevant information,

如果您的文本在单元格A1中,那么尝试使用这些公式来提取相关信息,

1. =LEFT(A1,(FIND(",",A1,1)-1))

2. =MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

3. =MAX(VALUE(MID(A1,FIND("GB",A1)-3,2)),VALUE(IFERROR(MID(A1,IFERROR(FIND("GB",A1,FIND("GB",A1)+1),0)-3,3),0)))&" GB"

#6

Using Regular Expressions:

使用正则表达式:

Assumptions:

• Phone Name starts at the beginning of the string and ends at the first comma

电话名称从字符串的开头开始,以第一个逗号结束

• Screen size is the first entry that starts with a number that might be decimal and ends with the "

屏幕大小是第一个条目,以一个可能是十进制的数字开头,以“

• Memory is the last entry that starts with an integer number ends with GB

内存是以整数开头的最后一个条目,以GB结尾

• For this, we will use the Replace method

为此,我们将使用Replace方法

---

Option Explicit
Function phone(S As String) As String
    Dim RE As Object
    Const sPat As String = "^([^,]+).*?(\b[\d.]+"").*(\b\d+\s+GB).*"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = sPat
    .Multiline = True
    phone = .Replace(S, "$1 $2 $3")
End With

End Function

Explanation of Regex and Replace Patterns

^([^,]+).*?(\b[\d.]+").*(\b\d+\s+GB).*

Options: Case sensitive; ^$ match at line breaks

选项:区分大小写; ^ $匹配在换行符

• Assert position at the beginning of a line ^

在行的开头断言位置^

• Match the regex below and capture its match into backreference number 1 ([^,]+)
  • Match any character that is NOT a “,” [^,]+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

    在一次和无限次之间,尽可能多次,根据需要回馈(贪婪)+

  匹配任何不是“,”[^,] +的字符,在一次和无限次之间,尽可能多次,根据需要返回(贪婪)+

匹配下面的正则表达式并将其匹配捕获到反向引号1([^,] +)匹配任何不是“,”[^,] +的字符,在一次和无限次之间,尽可能多次,根据需要返回(贪心)+

• Match any single character that is NOT a line break character .*?
  • Between zero and unlimited times, as few times as possible, expanding as needed (lazy) *?

  在零和无限次之间,尽可能少的时间,根据需要扩展(懒惰)*?

匹配任何不是换行符的单个字符。*?在零和无限次之间,尽可能少的时间,根据需要扩展(懒惰)*?

• Match the regex below and capture its match into backreference number 2 (\b[\d.]+")
  • Assert position at a word boundary \b

  断言字边界处的位置\ b

  • Match a single character present in the list below [\d.]+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

    在一次和无限次之间,尽可能多次,根据需要回馈(贪婪)+

    • A “digit” \d

    一个“数字”\ d

    • The literal character “.” .

    文字字符“。”。

  匹配下面列表中的单个字符[\ d。] +在一次和无限次之间,尽可能多次,根据需要返回(贪婪)+一个“数字”\ d文字字符“。”。

  • Match the character “"” literally "

  匹配字符“”字面意思“

匹配下面的正则表达式并将其匹配捕获到反向引用号2(\ b [\ d。] +“)在字边界处断言位置\ b匹配下面列表中存在的单个字符[\ d。] +在一个和无限制之间时间,尽可能多次,根据需要回馈(贪婪)+“数字”\ d字面字符“。”。匹配字符“”字面意思“

• Match any single character that is NOT a line break character .*
  • Between zero and unlimited times, as many times as possible, giving back as needed (greedy) *

  在零和无限次之间,尽可能多次,根据需要回馈(贪婪)*

匹配任何不是换行符的单个字符。*在零和无限次之间,尽可能多次,根据需要回馈(贪婪)*

• Match the regex below and capture its match into backreference number 3 (\b\d+\s+GB)
  • Assert position at a word boundary \b

  断言字边界处的位置\ b

  • Match a single character that is a “digit” \d+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

    在一次和无限次之间,尽可能多次,根据需要回馈(贪婪)+

  匹配单个字符“数字”\ d +在一次和无限次之间,尽可能多次,根据需要返回(贪婪)+

  • Match a single character that is a “whitespace character” \s+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

    在一次和无限次之间,尽可能多次,根据需要回馈(贪婪)+

  匹配单个字符“空白字符”\ s +在一次和无限次之间,尽可能多次,根据需要返回(贪婪)+

  • Match the character string “GB” literally GB

  匹配字符串“GB”字面上的GB

匹配下面的正则表达式并将其匹配捕获到反向引用号3(\ b \ d + \ s + GB)在字边界处断言位置\ b匹配单个字符“数字”\ d +在一个和无限次之间,尽可能多尽可能多的时间,根据需要回馈(贪婪)+匹配一个“空白字符”的单个字符\ s +在一次和无限次之间,尽可能多次,根据需要回馈(贪婪)+匹配字符串“ GB“字面意思GB

• Match any single character that is NOT a line break character .*
  • Between zero and unlimited times, as many times as possible, giving back as needed (greedy) *

  在零和无限次之间,尽可能多次,根据需要回馈(贪婪)*

匹配任何不是换行符的单个字符。*在零和无限次之间,尽可能多次,根据需要回馈(贪婪)*

$1 $2 $3

1美元2美元3美元

• Insert the text that was last matched by capturing group number 1 $1

通过捕获组号1 $ 1插入上次匹配的文本

• Insert the character “ ” literally

从字面上插入字符“”

• Insert the text that was last matched by capturing group number 2 $2

通过捕获组号2 $ 2插入上次匹配的文本

• Insert the character “ ” literally

从字面上插入字符“”

• Insert the text that was last matched by capturing group number 3 $3

通过捕获组号3 $ 3插入上次匹配的文本

Created with RegexBuddy

使用RegexBuddy创建