VBA how to transform a string of data into something else?

[MichaelRSnow]

Hi all, I've googled this but can't find a simple solution to the problem

I'm trying to convert a string that looks like this
X = 7/7/7/7/7/6/5/4/3/2 to X = 607001260012500124001230012200
Simple logic, each step between / is a 12 month step and value is a percentage, where the values are the same combine the months to the one value
5 x 7 would be 5 x 12 = 60, 7 is 7 percent so 700 (without the decimal place)
1 x 6 would be 1 x 12 = 12, 6 is 6 percent so 600 (without the decimal place)

I've see some split logic but struggle to find a way to merge the 5 steps of 7 in 1 step of 60700

Any assistance would be gratefully appreciated

 

[MichaelRSnow]

This creates the string as 12700127001270012700127001260012500124001230012200
But I would like to get it to 607001260012500124001230012200

Sub Code20Logic2()
Dim ERCStr As String
Dim x As Variant
Dim i As Long
Dim y As Variant


'Select First Cell
Sheets("Code020").Range("B2").Activate

'when to stop
Do Until ActiveCell.Value = ""

ERCStr = "/" & ActiveCell.Value

    x = Split(ERCStr, "/")
    For i = 1 To UBound(x)
        y = x(i) * 100
        
    ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value & "12" & y

    Next i

'Go down to next row
ActiveCell.Offset(1, 0).Activate
Loop


End Sub

 

[MichaelRSnow]

Hi, still trying to find a solution to this problem if anyone has any advice?

 

[Peter_SSs]

Does this user-defined function do what you want?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Function StrTransform(s As String) As String
  Dim Bits As Variant
  Dim i As Long, j As Long
 
  Bits = Split(s, "/")
  For i = 1 To UBound(Bits)
    If Bits(i) = Bits(i - 1) Then
      j = j + 1
    Else
      StrTransform = StrTransform & 12 * (j + 1) & Bits(i - 1) * 100
      j = 0
    End If
  Next i
  StrTransform = StrTransform & 12 * (j + 1) & Bits(UBound(Bits)) * 100
End Function

MichaelRSnow.xlsm
	A	B
1	7/7/7/7/7/6/5/4/3/2	607001260012500124001230012200
2	7/7/7/7/7/6/6/6/5/5/4	60700366002450012400
3	7/7/7/7/7/6/6/6/5/5/5	607003660036500
4	5	12500
5	10/10/10/10/6	48100012600
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=StrTransform(A1)

 

[MichaelRSnow]

Does this user-defined function do what you want?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Function StrTransform(s As String) As String
  Dim Bits As Variant
  Dim i As Long, j As Long
 
  Bits = Split(s, "/")
  For i = 1 To UBound(Bits)
    If Bits(i) = Bits(i - 1) Then
      j = j + 1
    Else
      StrTransform = StrTransform & 12 * (j + 1) & Bits(i - 1) * 100
      j = 0
    End If
  Next i
  StrTransform = StrTransform & 12 * (j + 1) & Bits(UBound(Bits)) * 100
End Function

MichaelRSnow.xlsm
	A	B
1	7/7/7/7/7/6/5/4/3/2	607001260012500124001230012200
2	7/7/7/7/7/6/6/6/5/5/4	60700366002450012400
3	7/7/7/7/7/6/6/6/5/5/5	607003660036500
4	5	12500
5	10/10/10/10/6	48100012600
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=StrTransform(A1)

Thanks for sharing this solution , works perfectly and i'll look to update my profile information

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.