Convert string of shoe size values?

Newbie here with a question...

Can you show me a way to convert a string of the FULL range of shoe sizes available, but modifying the size range to make it easy for the customer. For the range of sizes WHERE HALF-SIZES ARE AVAILABLE I need to shorten the display to LOWEST-HIGHEST. In my example "Current Value" below, any size with a ".5" means that is comes in a half-size. So I would want this converted to "6-12".

If the size does NOT list a ".5" half-size, then it remains listed comma separated... like  this 13, 14, 15, 16, 17, 18.

Current Value:
Size: 6, 6.5, 7, 7.5, 8, 8.5, 9, 9.5, 10, 10.5, 11, 11.5, 12, 12.5, 13, 14, 15, 16, 17, 18

Desired Result:
Size: 6-12, 13, 14, 15, 16, 17, 18

Can you help?
P-DaddyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
It isn't clear what the logic is. Why not list 11 and increments-  like 12 and 12.5?
P-DaddyAuthor Commented:
I'm trying to summarize the range of sizes that are available in whole and half-sizes. In my example that includes sizes 6-12 because 13 and higher are only available in whole sizes. Hope that helps clarify, thanks!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Use Split() on your original string using comma for the delimiter.  This will give you a one-dimensional array of values.

Then build up a new string variable as you walk the array one element at a time looking for the break in the half sizes.

Jim.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HooKooDooKuCommented:
First of all, it would seem to me that a relatively simple loop could be written that does a string copy.  You read the 1st size from the source string and write it to the destination string.  If the next size is a half size, write a '-' to the destination string.  Then continue reading the source string until you find a size that isn't followed by a half size.  That's when you follow the '-' with the previous size followed by the current size you found that doesn't have a half size.

But second, thinking of myself as say a customer at a web site shopping for shoes... "6-12,13,14,15,16" doesn't convey clearly to me that sizes 6-12 comes in half sizes.

Finally, there is a logic flaw in the notion that "6-12" should denote sizes 6 thru 12 come in have sizes.  "6-12" logically means everything from 6 to 12.  But 12.5 is outside of the range 6-12.  So logically, you should use "6-13" to denote that you have all half sizes BETWEEN size 6 and size 13.

But in the end, isn't it easier to simply have a size dropdown and list all available sizes?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Finally, there is a logic flaw in the notion that "6-12" should denote sizes 6 thru 12 come in have sizes.  "6-12" logically means everything from 6 to 12.  But 12.5 is outside of the range 6-12.  So logically, you should use "6-13" to denote that you have all half sizes BETWEEN size 6 and size 13.>>

 I see it the exact opposite; 6-12 tells me those are the sizes that have half sizes.  

Jim.
P-DaddyAuthor Commented:
Jim Dettman / HooKooDooKu -

Thanks for you input. First, industry standards dictate that displaying 6-12, 13, etc. means that 6-12 come in half sizes. If I took your suggestion and extended it to 6-13 then customers would expect size 13.5 to be available.

Second, since this is for catalog display I cannot display the full range in a dropdown. I'm trying to conserve space on the catalog page.

Third, if someone (HooKooDooKu, your approach seems the closest to a solution) could provide me with the function code to accomplish this, I'd appreciate it.

Thanks.
Gustav BrockCIOCommented:
Could be something like this:

sizes = "10, 11, 12, 13, 14, 15, 16, 17, 18"
sizes = "6, 6.5, 7, 7.5, 8, 8.5, 9, 9.5, 10, 10.5, 11, 11.5, 12, 12.5, 13, 14, 15, 16, 17, 18"
If Ubound(Split(sizes, ".")) = 0 Then
    SizeList = s
Else
    SizeList = Split(sizes, ",")(0) & "-" & LTrim(Split(sizes, ",")(2 * UBound(Split(sizes, ".")) - 2)) & Mid(Split(sizes, Split(sizes, ",")(2 * UBound(Split(sizes, "."))) - 1)(2), 3)
End If

Open in new window

Returns for the two samples:

    10, 11, 12, 13, 14, 15, 16, 17, 18
    6-12, 13, 14, 15, 16, 17, 18

Have fun!

/gustav
P-DaddyAuthor Commented:
Gustav Brock - Your solution looks promising...

Can you help me understand how to use your code? Can I use this directly in a query or do I have to make a VB Module and call it from my query? If you could walk me through exactly, I'd sure appreciate it!
Gustav BrockCIOCommented:
Copy and paste this into a module:
Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Dim SizeList As String

    If Nz(FullShoeSizeList) = "" Then Exit Function

    If Ubound(Split(FullShoeSizeList , ".")) = 0 Then
        SizeList = FullShoeSizeList 
    Else
        SizeList = Split(FullShoeSizeList , ",")(0) & "-" & LTrim(Split(FullShoeSizeList , ",")(2 * UBound(Split(FullShoeSizeList , ".")) - 2)) & Mid(Split(FullShoeSizeList , Split(FullShoeSizeList , ",")(2 * UBound(Split(FullShoeSizeList , "."))) - 1)(2), 3)
    End If

    ShortShoeSizeList= SizeList

End Function

Open in new window

Compile and save the module.

Now you can use the function in a query if field [sizes] holds your full size list:
    Select
        *, ShortShoeSizeList([sizes]) As SizeList,
    From
        tblYourTable

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
gustav,

  Nice solution and fast too<g>.

  I was trying to come up with something when the question first posted, refreshed, and bang, you were already there.

Jim.
Gustav BrockCIOCommented:
It looked too weird at first glance, but then your comment on using Split gave me an idea ...

/gustav
P-DaddyAuthor Commented:
Gustav -

Thanks, I'm closer with your solution. I created a query that looks like this...

SELECT tbl_TEMP_ZVPX_VARIATION.Variation, ShortShoeSizeList([Variation]) AS Result
FROM tbl_TEMP_ZVPX_VARIATION;

And I'm getting 2 errors ... a type mismatch error message and a subscript out of range error. The Debugger points to this line in the function....

SizeList = Split(FullShoeSizeList, ",")(0) & "-" & LTrim(Split(FullShoeSizeList, ",")(2 * UBound(Split(FullShoeSizeList, ".")) - 2)) & Mid(Split(FullShoeSizeList, Split(FullShoeSizeList, ",")(2 * UBound(Split(FullShoeSizeList, "."))) - 1)(2), 3)

Can you help me with the SQL?
Gustav BrockCIOCommented:
Maybe you have Null values in the field for some records? Or the size list is different from your samples.
What is the value of Variation?

/gustav
P-DaddyAuthor Commented:
Gustav - Yes, I re-tested with some cleaned up data and your code worked like a charm! Beautiful job and THANK YOU!
P-DaddyAuthor Commented:
Gustav - Thank you for providing an elegant solution to what I thought was a tough problem. You helped me a great deal!
Gustav BrockCIOCommented:
OK, here is a more solid method - not playing smart - that fits other ranges like:

 Size: 6, 6.5, 7, 7.5, 8, 8.5, 9, 10, 11, 12                                   Size: 6-9, 10, 11, 12
 Size: 6.5, 7, 7.5, 8, 8.5, 9, 10, 11, 12                                       Size: 6.5-9, 10, 11, 12
 Size: 6, 6.5, 7, 7.5, 8, 8.5, 11, 12                                             Size: 6-8.5, 11, 12
and even
 Size: 6.5, 7, 8, 11, 12                                                                Size: 6.5-7, 8, 11, 12
Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Dim SizeList            As String
    Dim Index               As Integer
    Dim FirstHalfNumber     As String
    Dim SecondHalfNumber    As String
    Dim Sizes               As Variant

    If Nz(FullShoeSizeList) = "" Then Exit Function
    
    Sizes = Split(Replace(FullShoeSizeList, " ", ""), ",")

    For Index = LBound(Sizes) To UBound(Sizes)
        If Index < UBound(Sizes) Then
            If Val(Sizes(Index + 1)) - Val(Sizes(Index)) < 1 Then
                ' Half-number sequence.
                If FirstHalfNumber = "" Then
                    FirstHalfNumber = Sizes(Index)
                Else
                    SecondHalfNumber = Sizes(Index + 1)
                End If
            Else
                ' Full-number sequence.
                If FirstHalfNumber <> "" Then
                    If SecondHalfNumber = "" Then
                        SecondHalfNumber = Sizes(Index)
                    End If
                    SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                    FirstHalfNumber = ""
                Else
                    If SizeList <> "" Then
                        SizeList = SizeList & ", "
                    End If
                    SizeList = SizeList & Sizes(Index)
                End If
            End If
        Else
            SizeList = SizeList & ", " & Sizes(Index)
        End If
    Next
    
    ShortShoeSizeList = SizeList

End Function

Open in new window

/gustav
P-DaddyAuthor Commented:
Hi Gustav - Thanks so much for the excellent follow up. I added your new function and tested against my data. Below are examples of the original string and the Result of the function. In some cases, I lost the word "Size:" for some reason. Also, the ranges aren't returned quite the way we

Variation1                                                          Result                              Desired Result
Size: 6, 6.5, 7, 7.5, 8, 8.5, 9, 10, 11, 12          6.5-9, 10, 11, 12             Size: 6-9, 10, 11. 12
Size: 6.5, 7, 7.5, 8, 8.5, 9, 10, 11, 12                  7-9, 10, 11, 12                Size: 6.5-9, 10, 11, 12
Size: 6.5, 7, 8, 11, 12                                          Size:6.5, 7, 8, 11, 12      Size: 6.5-7, 8, 11, 12

Are you able to tweak your function to get the Desired Result?
Gustav BrockCIOCommented:
When I run it here, it returns exactly the desired results, except for "Size: ".
You can have that prefixed in line 42:

    ShortShoeSizeList = "Size: " & SizeList

If your list is prefixed with "Size: ", chop it off in line 11:

    Sizes = Split(Replace(Mid(FullShoeSizeList, 7), " ", ""), ",")

/gustav
P-DaddyAuthor Commented:
Gustav -

You're a master! This is working perfectly except in one last scenario... that is, when the entire range comes in half-sizes. Here's what I'm getting in this scenario.

Variation1                                                          Result                              Desired Result
Size: 5, 5.5, 6, 6.5, 7, 7.5, 8                              Size: , 8                            Size: 5-8

Are you able to give it one last tweak to accommodate this scenario? Thanks!
Gustav BrockCIOCommented:
Yes:
Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Const SizePrefix        As String = "Size:"
    
    Dim SizeList            As String
    Dim Index               As Integer
    Dim FirstHalfNumber     As String
    Dim SecondHalfNumber    As String
    Dim SizePrefixed        As Boolean
    Dim Sizes               As Variant
    

    If Nz(FullShoeSizeList) = "" Then Exit Function
    
    ' Detect if list is prefixed with "Size:".
    If InStr(FullShoeSizeList, SizePrefix) = 1 Then
        FullShoeSizeList = Replace(FullShoeSizeList, SizePrefix, "")
        SizePrefixed = True
    End If
    
    Sizes = Split(Replace(FullShoeSizeList, " ", ""), ",")

    For Index = LBound(Sizes) To UBound(Sizes)
        If Index < UBound(Sizes) Then
            If Val(Sizes(Index + 1)) - Val(Sizes(Index)) < 1 Then
                ' Half-number sequence.
                If FirstHalfNumber = "" Then
                    FirstHalfNumber = Sizes(Index)
                Else
                    SecondHalfNumber = Sizes(Index + 1)
                End If
            Else
                ' Full-number sequence.
                If FirstHalfNumber <> "" Then
                    If SecondHalfNumber = "" Then
                        SecondHalfNumber = Sizes(Index)
                    End If
                    SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                    FirstHalfNumber = ""
                Else
                    If SizeList <> "" Then
                        SizeList = SizeList & ", "
                    End If
                    SizeList = SizeList & Sizes(Index)
                End If
            End If
        ElseIf SizeList = "" Then
            ' Series of half-number sizes only.
            SizeList = FirstHalfNumber & "-" & SecondHalfNumber
        Else
            ' Series of half-numbers and full numbers.
            SizeList = SizeList & ", " & Sizes(Index)
        End If
    Next
    
    If SizePrefixed = True Then
        SizeList = SizePrefix & " " & SizeList
    End If
    
    ShortShoeSizeList = SizeList

End Function

Open in new window

At this time I must have earned a pair of shoes!

Have nice weekend.
Gustav
P-DaddyAuthor Commented:
Gustav -

Your modified function works perfectly! You're a genius!

You definitely deserve my full gratitude and as a small gesture of thanks I'm also giving you a $100 shopping spree on our website. If you are shipping internationally, your order may be held up for an international freight quote. If international is the case, send me your order number and I'll see what I can do to help with shipping. (my email is pkruger@bsnsports.com)

I am going to email your certificate link directly to you, but if you don't receive it for some reason, email me and I'll make sure you get it!
Gustav BrockCIOCommented:
That's very kind of you. Let's see what we can find out. I am, however, in Denmark, so shipping costs may be prohibitive.
I'll Watch the inbox.

/gustav
IrogSintaCommented:
That is very cool, P-Daddy, and Gustav is a deserving recipient.  I'm just curious about something.  In your original post, you mentioned that your desired result was 6-12, 13, 14, 15, 16, 17, 18 even though there was a 12.5 in the list.  yet later on when you have  6, 6.5, 7, 7.5, 8, 8.5, 9, 10, 11, 12, your desired result is 6-9, 10, 11. 12.  It seems that this should be 6-8, 9, 10, 11, 12 in order to match your original request.  Or was your original request incorrect?

Ron
P-DaddyAuthor Commented:
Ron - You're 100% correct. My original requirements were changed by the business owner mid-stream and Gustav helped me by modifying his function to meet the new requirement.
IrogSintaCommented:
@Gustav, I think if you had stepped through the array backwards instead, you could have skipped all the IF exceptions.

Public Function ShortShoeSizeList(FullShoeSizeList As String) As String
     Dim aSizes         As Variant
     Dim i              As Integer
     Dim isHalfSize     As Boolean
     Dim sizeSkipped    As Boolean
     Dim sNewSizeList   As String
     Dim dCurrSize      As Double

     aSizes = Split(FullShoeSizeList, ",")
     
     For i = UBound(aSizes) To 0 Step -1
         dCurrSize = aSizes(i)
         isHalfSize = Right(dCurrSize, 2) = ".5"
         If i < UBound(aSizes) Then sizeSkipped = (aSizes(i + 1) - dCurrSize) > 1
         
         If isHalfSize And Not sizeSkipped Then Exit For
         sNewSizeList = ", " & dCurrSize & sNewSizeList
         If isHalfSize Then Exit For
     Next
     
     sNewSizeList = Mid(sNewSizeList, 3) 'removes preceding comma
     ShortShoeSizeList = aSizes(0) & IIf(isHalfSize, "-", "") & sNewSizeList
     
 End Function

Open in new window

6,6.5,7,7.5,8,8.5,9,10,11,12             6-9, 10, 11, 12
6.5,7,7.5,8,8.5,9,10,11,12                6.5-9, 10, 11, 12
6,6.5,7,7.5,8,8.5,10,11,12                6-8.5, 10, 11, 12
6.5,7,8,11,12                                     6.5-7, 8, 11, 12
5, 5.5, 6, 6.5, 7, 7.5, 8                       5-8

Note: the code above doesn't include replacing the "Size:" prefix.  I was just seeing how the middle section could be shortened.

Ron
Gustav BrockCIOCommented:
That seems like a possible method. There is often more than one way to skin a cat.

/gustav
P-DaddyAuthor Commented:
Gustav - Hate to ask, but I'm further along in production and ran into an issue with the function.  I am using this function...

Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Const SizePrefix        As String = "Size:"
   
    Dim SizeList            As String
    Dim Index               As Integer
    Dim FirstHalfNumber     As String
    Dim SecondHalfNumber    As String
    Dim SizePrefixed        As Boolean
    Dim Sizes               As Variant
   

    If Nz(FullShoeSizeList) = "" Then Exit Function
   
    ' Detect if list is prefixed with "Size:".
    If InStr(FullShoeSizeList, SizePrefix) = 1 Then
        FullShoeSizeList = Replace(FullShoeSizeList, SizePrefix, "")
        SizePrefixed = True
    End If
   
    Sizes = Split(Replace(FullShoeSizeList, " ", ""), ",")

    For Index = LBound(Sizes) To UBound(Sizes)
        If Index < UBound(Sizes) Then
            If Val(Sizes(Index + 1)) - Val(Sizes(Index)) < 1 Then
                ' Half-number sequence.
                If FirstHalfNumber = "" Then
                    FirstHalfNumber = Sizes(Index)
                Else
                    SecondHalfNumber = Sizes(Index + 1)
                End If
            Else
                ' Full-number sequence.
                If FirstHalfNumber <> "" Then
                    If SecondHalfNumber = "" Then
                        SecondHalfNumber = Sizes(Index)
                    End If
                    SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                    FirstHalfNumber = ""
                Else
                    If SizeList <> "" Then
                        SizeList = SizeList & ", "
                    End If
                    SizeList = SizeList & Sizes(Index)
                End If
            End If
        ElseIf SizeList = "" Then
            ' Series of half-number sizes only.
            SizeList = FirstHalfNumber & "-" & SecondHalfNumber
        Else
            ' Series of half-numbers and full numbers.
            SizeList = SizeList & ", " & Sizes(Index)
        End If
    Next
   
    If SizePrefixed = True Then
        SizeList = SizePrefix & " " & SizeList
    End If
   
    ShortShoeSizeList = SizeList

End Function

However, my data SOMETIMES contains a string that does not begin with "Size: " and in that case, I would like to simply return the string unchanged in the result. For example...

Variation1                                                          Result                              Desired Result
Age: Varsity                                                       -                                        Age: Varsity
Color: Black, White                                           Black, White                   Color: Black, White
Color: Black, Gold, White                                Color:Black-White          Color: Black, Gold, White
Size: 5.5, 6, 6.5, 7, 7.5, 8                                   Size: 5.5-8                       (already perfect)
Gustav BrockCIOCommented:
Yes, I would wrap it in a Select Case which will allow you later to expand the function to deal with other series:
Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Const PrefixDelimiter   As String = ":"
    
    Dim SizeList            As String
    Dim Index               As Integer
    Dim FirstHalfNumber     As String
    Dim SecondHalfNumber    As String
    Dim SizePrefixed        As Boolean
    Dim Sizes               As Variant
    Dim Prefix              As String

    If Nz(FullShoeSizeList) = "" Then Exit Function
    
    ' Detect prefix.
    If Split(FullShoeSizeList, PrefixDelimiter)(0) = FullShoeSizeList Then
        ' No prefix.
        SizeList = Trim(FullShoeSizeList)
    Else
        ' Get prefix.
        Prefix = Trim(Split(FullShoeSizeList, PrefixDelimiter)(0))
    End If
    
    Select Case Prefix
        Case ""
            ' Return FullShoeSizeList as is.
            SizeList = Trim(FullShoeSizeList)
        Case "Size"
            ' Compress FullShoeSizeList.
            Sizes = Split(Replace(Split(FullShoeSizeList, PrefixDelimiter)(1), " ", ""), ",")
        
            For Index = LBound(Sizes) To UBound(Sizes)
                If Index < UBound(Sizes) Then
                    If Val(Sizes(Index + 1)) - Val(Sizes(Index)) < 1 Then
                        ' Half-number sequence.
                        If FirstHalfNumber = "" Then
                            FirstHalfNumber = Sizes(Index)
                        Else
                            SecondHalfNumber = Sizes(Index + 1)
                        End If
                    Else
                        ' Full-number sequence.
                        If FirstHalfNumber <> "" Then
                            If SecondHalfNumber = "" Then
                                SecondHalfNumber = Sizes(Index)
                            End If
                            SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                            FirstHalfNumber = ""
                        Else
                            If SizeList <> "" Then
                                SizeList = SizeList & ", "
                            End If
                            SizeList = SizeList & Sizes(Index)
                        End If
                    End If
                ElseIf SizeList = "" Then
                    ' Series of half-number sizes only.
                    SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                Else
                    ' Series of half-numbers and full numbers.
                    SizeList = SizeList & ", " & Sizes(Index)
                End If
            Next
        Case "Age", "Color"
            SizeList = Trim(Split(FullShoeSizeList, ":")(1))
        Case Else
            SizeList = Trim(Split(FullShoeSizeList, ":")(1))
    End Select
    
    If Prefix <> "" Then
        SizeList = Prefix & PrefixDelimiter & " " & SizeList
    End If
    
    ShortShoeSizeList = SizeList

End Function

Open in new window

/gustav
P-DaddyAuthor Commented:
Perfect! That worked - thanks again!
Gustav BrockCIOCommented:
You are welcome!

/gustav
P-DaddyAuthor Commented:
Gustav - I need one more favor, please! Can your function be modified to IGNORE Size records which are non-numeric? For example...

Variation1                                                          
Size: XSM, SML, MED, LRG, XLG, XXL, 3XL                              

Current result
Size: XSM-XXL, 3XL

Desired Result (just return the orignal string)
Size: XSM, SML, MED, LRG, XLG, XXL, 3XL
Gustav BrockCIOCommented:
Yes, you can include a test for numeric values with IsNumeric. If not, let the function return the list as is:
Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Const PrefixDelimiter   As String = ":"
    
    Dim SizeList            As String
    Dim Index               As Integer
    Dim FirstHalfNumber     As String
    Dim SecondHalfNumber    As String
    Dim SizePrefixed        As Boolean
    Dim Sizes               As Variant
    Dim Prefix              As String

    If Nz(FullShoeSizeList) = "" Then Exit Function
    
    ' Detect prefix.
    If Split(FullShoeSizeList, PrefixDelimiter)(0) = FullShoeSizeList Then
        ' No prefix.
        SizeList = Trim(FullShoeSizeList)
    Else
        ' Get prefix.
        Prefix = Trim(Split(FullShoeSizeList, PrefixDelimiter)(0))
    End If
    
    Select Case Prefix
        Case ""
            ' Return FullShoeSizeList as is.
            SizeList = Trim(FullShoeSizeList)
        Case "Size"
            ' Compress FullShoeSizeList.
            Sizes = Split(Replace(Split(FullShoeSizeList, PrefixDelimiter)(1), " ", ""), ",")
        
            If Not IsNumeric(Sizes(LBound(Sizes))) Then
                ' Series like: XSM, SML, MED, LRG, XLG, XXL, 3XL, etc.
                ' Return non-concatenated list.
                SizeList = Trim(Split(FullShoeSizeList, ":")(1))
            Else
                ' Series like: 6, 6.5, 7, etc.
                For Index = LBound(Sizes) To UBound(Sizes)
                    If Index < UBound(Sizes) Then
                        If Val(Sizes(Index + 1)) - Val(Sizes(Index)) < 1 Then
                            ' Half-number sequence.
                            If FirstHalfNumber = "" Then
                                FirstHalfNumber = Sizes(Index)
                            Else
                                SecondHalfNumber = Sizes(Index + 1)
                            End If
                        Else
                            ' Full-number sequence.
                            If FirstHalfNumber <> "" Then
                                If SecondHalfNumber = "" Then
                                    SecondHalfNumber = Sizes(Index)
                                End If
                                SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                                FirstHalfNumber = ""
                            Else
                                If SizeList <> "" Then
                                    SizeList = SizeList & ", "
                                End If
                                SizeList = SizeList & Sizes(Index)
                            End If
                        End If
                    ElseIf SizeList = "" Then
                        ' Series of half-number sizes only.
                        SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                    Else
                        ' Series of half-numbers and full numbers.
                        SizeList = SizeList & ", " & Sizes(Index)
                    End If
                Next
            End If
        Case "Age", "Color"
            SizeList = Trim(Split(FullShoeSizeList, ":")(1))
        Case Else
            SizeList = Trim(Split(FullShoeSizeList, ":")(1))
    End Select
    
    If Prefix <> "" Then
        SizeList = Prefix & PrefixDelimiter & " " & SizeList
    End If
    
    ShortShoeSizeList = SizeList

End Function

Open in new window

/gustav
P-DaddyAuthor Commented:
Thanks again Gustav. I am running into a...

    Run-time error '9':

    Subscript out of range

... error. I can click through it and the results of the function look good, but do you know why it is throwing that error?
Gustav BrockCIOCommented:
What is your input size string when you see that error?
Your example string processes nicely here.

/gustav
Gustav BrockCIOCommented:
Did you find out?

/gustav
P-DaddyAuthor Commented:
Sorry, I was called away to something else. Let me double check and see if we're still having that issue.
P-DaddyAuthor Commented:
Okay, so we still get the run-time error when the function runs, but we can click through the error and it runs fine.

Should I just do that, or should we try to fix it? (If you think we should fix it, can you tell me how to check the size of the input string for you? Or should I send you the table data?)
Gustav BrockCIOCommented:
You could send me the table data. There must be some value lists that fool the function.

/gustav
P-DaddyAuthor Commented:
Gustav - thanks! I'm attaching a .csv of the table column I'm running your function against. If you could take a look, I'd appreciate it!
BSN-size-variation.csv
Gustav BrockCIOCommented:
It was those records holding no list, only:

    Size:

Another error was caused by a list like:

    4, 4 1/2, 5, 5 1/2, 6, 6 1/2, 7, 7 1/2, 8, 8 1/2, 9

Also, some lists have a trailing dot. This is also removed now.
See the in-line notes:
Public Function ShortShoeSizeList(ByVal FullShoeSizeList As Variant) As String

    Const PrefixDelimiter   As String = ":"
    
    Dim SizeList            As String
    Dim Index               As Integer
    Dim FirstHalfNumber     As String
    Dim SecondHalfNumber    As String
    Dim SizePrefixed        As Boolean
    Dim Sizes               As Variant
    Dim Prefix              As String

    If Trim(Nz(FullShoeSizeList)) = "" Then Exit Function
    
    ' Convert size list like:
    '   4, 4 1/2, 5, 5 1/2, 6, 6 1/2, 7, 7 1/2, 8, 8 1/2, 9
    ' to:
    '   4, 4.5, 5, 5.5, 6, 6.5, 7, 7.5, 8, 8.5, 9
    FullShoeSizeList = Replace(FullShoeSizeList, " 1/2", ".5")
    
    ' Remove a trailing dot.
    If Right(FullShoeSizeList, 1) = "." Then
        FullShoeSizeList = Left(FullShoeSizeList, Len(FullShoeSizeList) - 1)
    End If
    
    ' Detect prefix.
    If Split(FullShoeSizeList, PrefixDelimiter)(0) = FullShoeSizeList Then
        ' No prefix.
        SizeList = Trim(FullShoeSizeList)
    Else
        ' Get prefix.
        Prefix = Trim(Split(FullShoeSizeList, PrefixDelimiter)(0))
    End If
    
    Select Case Prefix
        Case ""
            ' Return FullShoeSizeList as is.
            SizeList = Trim(FullShoeSizeList)
        Case "Size"
            ' Compress FullShoeSizeList.
            Sizes = Split(Replace(Split(FullShoeSizeList, PrefixDelimiter)(1), " ", ""), ",")
        
            If UBound(Sizes) = -1 Then
                ' Empty size list, though prefixed.
                ' Return empty list with no prefix.
                Prefix = ""
                SizeList = ""
            ElseIf Not IsNumeric(Sizes(LBound(Sizes))) Then
                ' Series like: XSM, SML, MED, LRG, XLG, XXL, 3XL, etc.
                ' Return non-concatenated list.
                SizeList = Trim(Split(FullShoeSizeList, ":")(1))
            Else
                ' Series like: 6, 6.5, 7, etc.
                For Index = LBound(Sizes) To UBound(Sizes)
                    If Index < UBound(Sizes) Then
                        If Val(Sizes(Index + 1)) - Val(Sizes(Index)) < 1 Then
                            ' Half-number sequence.
                            If FirstHalfNumber = "" Then
                                FirstHalfNumber = Sizes(Index)
                            Else
                                SecondHalfNumber = Sizes(Index + 1)
                            End If
                        Else
                            ' Full-number sequence.
                            If FirstHalfNumber <> "" Then
                                If SecondHalfNumber = "" Then
                                    SecondHalfNumber = Sizes(Index)
                                End If
                                SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                                FirstHalfNumber = ""
                            Else
                                If SizeList <> "" Then
                                    SizeList = SizeList & ", "
                                End If
                                SizeList = SizeList & Sizes(Index)
                            End If
                        End If
                    ElseIf SizeList = "" Then
                        ' Series of half-number sizes only.
                        SizeList = FirstHalfNumber & "-" & SecondHalfNumber
                    Else
                        ' Series of half-numbers and full numbers.
                        SizeList = SizeList & ", " & Sizes(Index)
                    End If
                Next
            End If
        Case "Age", "Color"
            SizeList = Trim(Split(FullShoeSizeList, ":")(1))
        Case Else
            SizeList = Trim(Split(FullShoeSizeList, ":")(1))
    End Select
    
    If Prefix <> "" Then
        SizeList = Prefix & PrefixDelimiter & " " & SizeList
    End If
    
    ShortShoeSizeList = SizeList

End Function

Open in new window

/gustav
P-DaddyAuthor Commented:
I think that fixed it! Thanks again Gustav.
Gustav BrockCIOCommented:
You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.