VBA extracting values in a string and assigning

[Martynrbell]

Hi All.
I have some data in two columns that are there similar
an example been

DA2*635320*11845*50*1

DA2*635455*11848*135*3

<tbody>
</tbody>

Im writing an If statement where If the cell begins with DA2 it compares the two cells and returns the difference.

The only way i can thing of doing it is by extracting the numbers and assigning them two a variable (my terminology is well off i know)
So for example VBA reads the cells and creates the following

ADA21 = 635320
ADA22 = 11845

BDA21 = 635455
BDA22 = 11848

I should then be able to do ADA21 - BDA21 and ADA22 - BDA22 to get two separate values.

Can anyone help out with this or even suggest a better way if there is one ?

Thanks in advance

 

[JackDanIce]

This assumes your data is in columns A and B, the differences in values will print to columns C and D and there is a header in row 1, try:

Sub M1()

    Dim x           As Long
    Dim arr         As Variant
    Dim t(1 To 2)   As Variant
    
    x = Cells(Rows.Count, 1).End(xlUp).Row - 1
    arr = Cells(2, 1).Resize(x, 4).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        t(1) = Split(arr(x, 1), "*")
        t(2) = Split(arr(x, 2), "*")
        If t(1)(0) = t(2)(0) Then
            arr(x, 3) = t(1)(1) - t(2)(1)
            arr(x, 4) = t(1)(2) - t(2)(2)
        End If
    Next x
    
    Cells(2, 1).Resize(UBound(arr, 1), 4).Value = arr
    
    Erase arr: Erase t
    
End Sub

 

[Fluff]

Another way with a UDF

Function Martyn(St1 As String, st2 As String, Itm As Long) As Double
   Martyn = Split(st2, "*")(Itm) - Split(St1, "*")(Itm)
End Function

Book1
	A	B	C	D	E	F
2	DA2*635320*11845*50*1	DA2*635455*11848*135*3	135	3	85	2
Data

Cell Formulas
Range	Formula
C2	=Martyn($A$2,$B$2,COLUMN(A1))

just drag the formula in C2 to the right

 

[Martynrbell]

This is a bit late i apologise.

I have used both of these as i can apply them both.

Thanks alot

 

[Fluff]

You're welcome & thanks for the feedback