4

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

Improve this question
7
  • can you explain why 64 gb and not 4 gb. Is it the second from the end, the second after the ()? How will excel know which to return? Commented Oct 9, 2018 at 13:27
  • The 64 GB represents the storage of the phone, which has to be included in the name. This is always two digits which I think can be used to distinguish between 4 GB. It is always after the () but the 4 GB isn't always included. Commented Oct 9, 2018 at 13:31
  • Will there always be a space between the size and "GB" or "MP"? Commented Oct 9, 2018 at 13:34
  • Yes, there will always be a space between the value and the "GB" and "MP" Commented Oct 9, 2018 at 13:36
  • Can you please add more examples of the input and expected output? Commented Oct 9, 2018 at 13:39

6 Answers 6

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

You're pattern also picks up 4 GB. Change to .Pattern = "[0-9]{2,} GB" to pick up 2 or more digits before the GB. See example
That would work mostly but there are phones coming out now with over 10 GB of RAM which means they would still be picked before the storage GB. It depends on what types of phones @Fehrnovic has in his sheet.
@Taazar - Good point. Try this pattern to match the last occurrence: .Pattern = "([0-9]+ GB)(?!.*[0-9]{2,} GB)" See this link for example. I added a new test line with RAM over 2 digits.
Thank you so much to all you guys, this helped a ton :)
4

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

enter image description here

Improve this answer

7 Comments

Problem is as @Fehrnovic has said that 4GB may not always be there and in a case like that this code will grab the 20 MP instead.
@Taazar, thanks for the heads up; adjusted above to suit.
That fix does help for some but it means that if the storage value is stored in the 4th section/vals(3) or before then it will be missed by your code.
Parsing text requires a finite number of conditions/criteria/ You can't keep piling on additional scenarios.
Terribly sorry, turns out when I copied the code over I missed a bit at the end. Again I'm very sorry.
|
2

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.

Improve this answer

2 Comments

Problem is as @Fehrnovic has said that 4GB may not always be there and in a case like that this code will grab the 20 MP instead
Thank you, but this only works when the 4 GB is present. This is not always the case and so the 20 MP is extracted instead. But using an array like that seems like a good idea
2

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.

Improve this answer

1 Comment

Pixels also won't be an issue as they don't have "GB" in that section.
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,

enter image description here

enter image description here

  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"

Improve this answer

Comments

2

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

enter image description here

Explanation of Regex and Replace Patterns

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

Options: Case sensitive; ^$ match at line breaks

$1 $2 $3

Created with RegexBuddy

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.