Replace All strings starting with a number ending in 'cm'

[alex0182828]

I need remove string starting in a number and ending in 'cm' or 'CM' and replace with a # symbol.

Number can be anything and the string between the first number and the cm can be or variable length.

There will only be one occurance of this pattern in each cell.

Made example table can anyone help ? Thanks

Book8
	A	B
1	Source	Result
2	Frog *** Halskette 34,5 - 56cm	Frog *** Halskette #
3	Rund Pavé - Fassung Nosiw 20,5 - 81cm	Rund Pavé - Fassung Nosiw #
4	adasd 85,5 - 91CM Fuchwanz Kette	adasd # Fuchwanz Kette
5	Rosoldet CAAAsf 35,5 - 71 cm Fuchsschwanz Kette	Rosoldet CAAAsf # Fuchsschwanz Kette
Sheet1

 

[Akuini]

@alex0182828
Did you try my code in post #7?

 

[Georgiboy]

I was working to this:

I need remove string starting in a number and ending in 'cm' or 'CM' and replace with a # symbol.

34,5 did not end with a "cm" so it was kept. I see now what you mean now.

Perhaps more like the below:

Book1
	A	B
1	Source	Result
2	Frog *** Halskette 34,5 - 56cm	Frog *** Halskette #
3	Rund Pavé - Fassung Nosiw 20,5 - 81cm	Rund Pavé-Fassung Nosiw #
4	adasd 85,5 - 91CM Fuchwanz Kette	adasd # Fuchwanz Kette
5	Rosoldet CAAAsf 35,5 - 71 cm Fuchsschwanz Kette	Rosoldet CAAAsf # Fuchsschwanz Kette
6	8 frog cat 90,5 - 95,5cm	8 frog cat #
7	frog 7 bat 67 - 95cm happy	frog 7 bat # happy
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=LET(data,SUBSTITUTE(SUBSTITUTE(A2:A7," cm","cm")," - ","-"), c, MAX(BYROW(data,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x," ",""))+1))), xml, "<t><s>" & SUBSTITUTE(data," ","</s><s>") & "</s></t>", f,IFERROR(FILTERXML(xml,"//s[" & SEQUENCE(,c) & "]"),""), m,MAP(f,LAMBDA(x,IF(RIGHT(x,2)="cm","#",x))), BYROW(m,LAMBDA(x,TEXTJOIN(" ",,x))))
Dynamic array formulas.

Or

Book1
	A	B
1	Source	Result
2	Frog *** Halskette 34,5 - 56cm	Frog *** Halskette #
3	Rund Pavé - Fassung Nosiw 20,5 - 81cm	Rund Pavé-Fassung Nosiw #
4	adasd 85,5 - 91CM Fuchwanz Kette	adasd # Fuchwanz Kette
5	Rosoldet CAAAsf 35,5 - 71 cm Fuchsschwanz Kette	Rosoldet CAAAsf # Fuchsschwanz Kette
6	8 frog cat 90,5 - 95,5cm	8 frog cat #
7	frog 7 bat 67 - 95cm happy	frog 7 bat # happy
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTJOIN(" ",,BYCOL(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2," cm","cm")," - ","-")," "),LAMBDA(x,IF(RIGHT(x,2)="cm","#",x))))

 

[alex0182828]

Try this:

Sub alex0182828_1()

Dim i As Long, n As Long
Dim tx As String
Dim va

va = Range("A2", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)
For i = 1 To UBound(va, 1)
    If InStr(1, va(i, 1), "cm", vbTextCompare) > 0 Then
        tx = Trim((va(i, 1)))
        tx = Replace(tx, "CM", "cm")
            arx = Split(tx, "cm")
            ary = Split(Trim(arx(0)), " ")
      
        n = UBound(ary)
            tx = ary(n - 2) & " - " & ary(n)
                va(i, 1) = Replace(arx(0), tx, " # ") & arx(1)
    End If
Next


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

It worked on your small sample:

Book1
	A	B
1	Source
2	Frog *** Halskette 34,5 - 56cm	Frog *** Halskette #
3	Rund Pavé - Fassung Nosiw 20,5 - 81cm	Rund Pavé - Fassung Nosiw #
4	adasd 85,5 - 91CM Fuchwanz Kette	adasd # Fuchwanz Kette
5	Rosoldet CAAAsf 35,5 - 71 cm Fuchsschwanz Kette	Rosoldet CAAAsf # Fuchsschwanz Kette
6	8 frog cat 90,5 - 95,5cm	8 frog cat #
7	frog 7 bat 67 - 95cm happy	frog 7 bat # happy
8
Sheet2

but it won't work if you have more than one "cm", such as:
pacman frog 7 bat 67 - 95cm happy

Sorry for the delayed reply I got this to work once but now i keep getting the error type mismatch even on the demo data - any ideas ?

 

[Sanjeev1976]

Try :

=SUBSTITUTE(TEXTJOIN(" ",,MAP(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2," - ","-")," cm","cm")," "),LAMBDA(x,IF(RIGHT(x,2)="cm","#",x)))),"-"," - ")

 

[Akuini]

Sorry for the delayed reply I got this to work once but now i keep getting the error type mismatch even on the demo data - any ideas ?

Try this one:

Sub alex0182828_2()

Dim i As Long, n As Long
Dim tx As String
Dim va

va = Range("A2", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)
For i = 1 To UBound(va, 1)
    zz = va(i, 1)
    On Error Resume Next
    If InStr(1, va(i, 1), "cm", vbTextCompare) > 0 Then
        tx = Trim((va(i, 1)))
        tx = Replace(tx, "CM", "cm")
            arx = Split(tx, "cm")
            ary = Split(Trim(arx(0)), " ")
        
        n = UBound(ary)
            tx = ary(n - 2) & " - " & ary(n)
                va(i, 1) = Replace(arx(0), tx, " # ") & arx(1)
    End If
    If Err.Number > 0 Then
        va(i, 1) = "x"
        Debug.Print zz
        On Error GoTo 0
    End If
Next

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

If there is an error in a row, the row will be marked with an "x".
In the immediate window, you can see which rows have errors. You can copy and paste them into your post so that I can check them.

 

[alex0182828]

Thanks everyone formulas working well. The only edge case i got was when there is more than two hyphens in the source data but for now i have fixed this manually and dont need any new formulas

 

[Georgiboy]

Glad we could help, thanks for the feedback.

 

[Sanjeev1976]

Thanks everyone formulas working well. The only edge case i got was when there is more than two hyphens in the source data but for now i have fixed this manually and dont need any new formulas

Try the formula in Post 14, which takes care of this requirement

 

[Peter_SSs]

Try the formula in Post 14, which takes care of this requirement

Depends on what the original data could be like. It could introduce spaces that were not present in the original data like B2 below.
But it could be tweaked to account for that with something like C2.

23 07 07.xlsm
	A	B	C
1	Source
2	Rund Pavé-Fassung Nosiw 20,5 - 81cm	Rund Pavé - Fassung Nosiw #	Rund Pavé-Fassung Nosiw #
Sheet2 (2)

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(TEXTJOIN(" ",,MAP(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2," - ","-")," cm","cm")," "),LAMBDA(x,IF(RIGHT(x,2)="cm","#",x)))),"-"," - ")
C2	C2	=SUBSTITUTE(TEXTJOIN(" ",,MAP(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2," - ","---")," cm","cm")," "),LAMBDA(x,IF(RIGHT(x,2)="cm","#",x)))),"---"," - ")