# 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?
###### Who is Participating?

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.

RetiredCommented:
It isn't clear what the logic is. Why not list 11 and increments-  like 12 and 12.5?
Author 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!
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.
Commented:
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?
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.
Author 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.
CIOCommented:
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
``````
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
Author 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!
CIOCommented:
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
``````
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
``````
/gustav

Experts Exchange Solution brought to you by

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

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.
CIOCommented:
It looked too weird at first glance, but then your comment on using Split gave me an idea ...

/gustav
Author 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?
CIOCommented:
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
Author Commented:
Gustav - Yes, I re-tested with some cleaned up data and your code worked like a charm! Beautiful job and THANK YOU!
Author Commented:
Gustav - Thank you for providing an elegant solution to what I thought was a tough problem. You helped me a great deal!
CIOCommented:
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
``````
/gustav
Author 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?
CIOCommented:
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
Author 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!
CIOCommented:
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
``````
At this time I must have earned a pair of shoes!

Have nice weekend.
Gustav
Author 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!
CIOCommented:
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
Commented:
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
Author 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.
Commented:
@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
``````
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
CIOCommented:
That seems like a possible method. There is often more than one way to skin a cat.

/gustav
Author 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)
CIOCommented:
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
``````
/gustav
Author Commented:
Perfect! That worked - thanks again!
CIOCommented:
You are welcome!

/gustav
Author 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
CIOCommented:
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
``````
/gustav
Author 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?
CIOCommented:
What is your input size string when you see that error?
Your example string processes nicely here.

/gustav
CIOCommented:
Did you find out?

/gustav
Author Commented:
Sorry, I was called away to something else. Let me double check and see if we're still having that issue.
Author 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?)
CIOCommented:
You could send me the table data. There must be some value lists that fool the function.

/gustav
Author 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
CIOCommented:
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
``````
/gustav
Author Commented:
I think that fixed it! Thanks again Gustav.
CIOCommented:
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.