Comment #3

JADELottery — Sat, 14 Jul 2018 14:07:47 GMT

Select and Copy the code between the horizontal lines. Open Excel with a new file. Next, press Alt + F11 to open the code editor. Now, menu select Insert then Module. This will create a module code page. Finally, select anywhere on the module page and Paste the code you just copied from the blog. Tada, you now have the function available to use. Close the code editor and try typing the =StringDecompress( , ) funct... [ More ]

Comment #2

JADELottery — Sat, 14 Jul 2018 02:08:25 GMT

What it

Comment #1

lakerben — Fri, 13 Jul 2018 22:12:04 GMT

Can you post it

Original Blog Entry: Our Excel StringDecompress routine.

JADELottery — Fri, 13 Jul 2018 17:41:16 GMT

Well, here's what we're usin'.

____________________________________________________________________________________________________

Function StringDecompress(str As String, n As Integer) As String

On Error GoTo exitfunction

Dim temp As String

Dim c, i, j, strlen As Integer

Dim nums() As Long: ReDim nums(n)

strlen = Len(Mid(str, 2))

temp =

For i = 0 To strlen - 1

temp = temp Mid(str, strlen - i + 1, 1)

Next i

strlen = Application.WorksheetFunction.RoundUp(strlen / 2, 0)

If (n 2) Or (n strlen) Then GoTo exitfunction

For i = 0 To strlen - 1

nums(n - 1 - i) = Val(Mid(temp, 2 * i + 2, 1) Mid(temp, 2 * i + 1, 1))

Next i

c = 0

i = 0

For j = (n - strlen) To (n - 1)

If c (n - strlen) Then

If nums(j) = 9 Then

nums(i) = nums(j)

i = i + 1

Else

nums(i) = Int(nums(j) / 10)

nums(i + 1) = nums(j) - (10 * nums(i))

i = i + 2

c = c + 1

End If

End If

Next j

temp =

For i = 0 To n - 1

temp = temp Format(nums(i), 00 )

Next i

StringDecompress = temp

Exit Function

exitfunction:

StringDecompress = Error

End Function

____________________________________________________________________________________________________

The data pasted into Excel must be of a format that has a single non-numeric character at the beginning of each set of compressed numbers.

In this case, we used the middot ' ' character, but it can be anything other than '-', '+' or a natural space hex code 20 or escape code \x20.

This causes Excel to treat the string of numbers as a string and prevents Excel from trying to convert the string of numbers into an actual number in Excel.

The usage is =StringDecpompress(str, n), where str is the cell containing the compressed string of numbers and n is the pick size of the desired separated number set.

Below is an example where n = 5:

Month Day Year Compressed #s Decompressed #s

07 12 2018 318192425 03 18 19 24 25

07 11 2018 311162230 03 11 16 22 30

07 10 2018 613141531 06 13 14 15 31

07 09 2018 315282931 03 15 28 29 31

07 08 2018 35161721 03 05 16 17 21

07 07 2018 68223031 06 08 22 30 31

07 06 2018 37151631 03 07 15 16 31

07 05 2018 1416232930 14 16 23 29 30

07 04 2018 3582022 03 05 08 20 22

07 03 2018 1391427 01 03 09 14 27

07 02 2018 611202629 06 11 20 26 29

07 01 2018 27102129 02 07 10 21 29

06 30 2018 356927 03 05 06 09 27

06 29 2018 5671724 05 06 07 17 24

06 28 2018 110131531 01 10 13 15 31

06 27 2018 516192829 05 16 19 28 29

06 26 2018 14142328 01 04 14 23 28

06 25 2018 810132629 08 10 13 26 29

06 24 2018 26122430 02 06 12 24 30

06 23 2018 27161723 02 07 16 17 23

06 22 2018 914171826 09 14 17 18 26

06 21 2018 45272831 04 05 27 28 31

06 20 2018 1521242629 15 21 24 26 29

06 19 2018 720222426 07 20 22 24 26

06 18 2018 615202325 06 15 20 23 25

06 17 2018 1672227 01 06 07 22 27

... [ More ]