Trouble with Excel syntax

[dan_m101]

I've tried different things but I'm having trouble finding the correct syntax to accomplish the following

In column A I have text like

brut-230x340x450x50:890_capital
sale-120x100:450_trim

there's always a "-" at the beginning of the line followed by a "_" towards the end

these markers do not appear elsewhere on the line, ever

I would like to count the number of x and : between the 2 markers, for example 4 is the answer for the first line and 2 for the second

I can't seem to get the correct vba coding unfortunately

Thank you for any help

 

[Akuini]

Try:

Sub a1181660a()
Dim i As Long
Dim tx As String
Dim va
va = Range("A2", Cells(Rows.Count, "A").End(xlUp))

For i = 1 To UBound(va, 1)
    tx = Split(va(i, 1), "-")(1)
    tx = Split(tx, "_")(0)
    tx = Replace(tx, ":", "x")
    va(i, 1) = UBound(Split(tx, "x"))
Next

Range("B2").Resize(UBound(va, 1), 1) = va
End Sub

Book1
	A	B
1
2	brut-230x340x450x50:890_capital	4
3	sale-120x100:450_trim	2
4	sale-120x100:450_trim 2x2:	2
Sheet1

 

[dan_m101]

thx Akuini, I'm also considering writing a function to handle text in any cell, would the coding look significantly different? thanks again

 

[Akuini]

I'm also considering writing a function to handle text in any cell,

Do you mean a UDF?

Function dan_m101(c As Range)
Dim tx As String

    tx = Split(c, "-")(1)
    tx = Split(tx, "_")(0)
    tx = Replace(tx, ":", "x")
    dan_m101 = UBound(Split(tx, "x"))

End Function

Book1
	A	B
1
2	brut-230x340x450x50:890_capital	4
3	sale-120x100:450_trim	2
4	sale-120x100:450_trim 2x2:	2
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=dan_m101(A2)

 

[Peter_SSs]

I'm also considering writing a function to handle text in any cell

In that case, why not use native worksheet functions.
For example, if you have MS365 you could use column B, otherwise column C. Speaking of versions, 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’)

If you still really want a UDF, you could put all of Akuini's clearly set-out steps into a single line

Function Dan(s As String) As Long
  Dan = UBound(Split(Replace(Split(Split(s, "-")(1), "_")(0), ":", "x"), "x"))
End Function

dan_m101.xlsm
	A	B	C	D
1	brut-230x340x450x50:890_capital	4	4	4
2	sale-120x100:450_trim	2	2	2
3	sale-120x100:450_trim 2x2:	2	2	2
4	sale-6_dcd	0	0	0
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LET(s,SUBSTITUTE(REPLACE(LEFT(A1,FIND("_",A1)),1,FIND("-",A1),""),":","x"),LEN(s)-LEN(SUBSTITUTE(s,"x","")))
C1:C4	C1	=LEN(SUBSTITUTE(REPLACE(LEFT(A1,FIND("_",A1)),1,FIND("-",A1),""),":","x"))-LEN(SUBSTITUTE(SUBSTITUTE(REPLACE(LEFT(A1,FIND("_",A1)),1,FIND("-",A1),""),":","x"),"x",""))
D1:D4	D1	=Dan(A1)