Extract specific part of String

Question

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:

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

To be formatted as

P10 5.1" 64 GB

I have succeeded in extracting the first two elements of the formatted name using Split and 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.

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å

Expected Output:

iPhone iPhone SE 4" 16 GB

Answer 1

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
• For this, we will use the Replace method

---

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) +
• 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
  • 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
    • The literal character “.” .
  • Match the character “"” literally "
• 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
  • Match a single character that is a “digit” \d+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • 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) +
  • Match the character string “GB” literally 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

• Insert the text that was last matched by capturing group number 1 $1
• Insert the character “ ” literally
• Insert the text that was last matched by capturing group number 2 $2
• Insert the character “ ” literally
• Insert the text that was last matched by capturing group number 3 $3

Created with RegexBuddy

Answer 2

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,

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"

Answer 3

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.

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.

Answer 4

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:

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:

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.

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.

Answer 5

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

Answer 6

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..

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