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

 

[davesexcel]

Try this:

=REPLACE(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),FIND("cm",LOWER(A2))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+2,"#")

 

[alex0182828]

Thanks works perfect for the examples i have but it highlighted i missed out that there can be other numbers in the cell so examples like

8 frog cat 90,5 - 95,5cm -> 8 frog cat #
frog 7 bat 67 - 95cm happy -> frog 7 bat # happy

 

[alex0182828]

Wondering if anyone can help me with this

 

[Akuini]

Wondering if anyone can help me with this

Is it ok to use vba?

 

[alex0182828]

Willing to do anything says a desperate person.

Im running up to date mac office 365 is thats important to know.

A

 

[Akuini]

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

 

[Georgiboy]

How about (if you have the MAP function):

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

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

Same result with BYCOL:

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

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

 

[Georgiboy]

Here is a one cell formula option:

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

Cell Formulas
Range	Formula
B2:B7	B2	=LET(data, 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.

 

[alex0182828]

I think maybe there is some confusion about the use of the # symbol in my example. The results in both your solutions @Georgiboy dont match mine / the results should show exactly as in my demo sheet. i.e a # be inserted.

My Soure data in A2 is

Frog *** Halskette 34,5 - 56cm

the result for this should be

Frog *** Halskette #

where as the result in both your was

Frog *** Halskette 34,5 - #