removing words only from the left..

[nafri]

Hi

Got a small issue.

I need to remove specific words from a coloumn but only if the characters of the first word in the coloumn matches them.

In the folowing example i only wish to remove the first blah ,bla,pan,loc,sugar not the second if it exist.Not all words have repeaters.The important thing is i only wish to remove the word if the characters match the first word on the left .

A1
blah23eweew blah ---- this becomes 23eweew blah
bla23dddd bla --- this becomes 23dddd bla
pandfgsswer pan --- this becomes dfgsswer pan
loc2341 loc --- this becomes 2341 loc
sugar23wewew sugar --- this becomes 23wewew sugar

kind regards

nafri

 

[nafri]

Try this UDF


Function Tidy(s As String) As String
Dim arr
Dim p As String
Dim i As Long

Static RegEx As Object

If RegEx Is Nothing Then
Set RegEx = CreateObject("VBScript.RegExp")
arr = Array("toslcdtv", "samlcdtv", "sanlcd", "shalcdtv", "shalcd", "PHILCD", "CELLO", "PHITFT", _
"HANNSG", "IIY", "NECTFT", "NECLCD", "BTE", "lgp", "ppd", "samp", "pand", _
"sam", "peer", "lox", "sony", "chief", "bosch", "onk", "phi", "hk", "pan", "lg")
For i = LBound(arr) To UBound(arr)
p = p & "|" & arr(i)
Next i
p = "^(" & Replace(p, "|", "", 1, 1) & ")"
With RegEx
.Global = True
.IgnoreCase = True
.Pattern = p
End With
End If
Tidy = RegEx.Replace(s, "")
End Function

Excel Workbook
	F	G
1	toslcdtva	a
2	samlcdtvzzzz	zzzz
3	sanlcd
4	abc shalcdtv	abc shalcdtv
5	hhhhshalcdc	hhhhshalcdc
Sheet1

Many thanks .. to all.. this solution works for me.

cheers
nafri

 

[Peter_SSs]

Thanks for the feedback.

If your 'Removal List' is in the sheet as per Ron's example in post #9, then rather than repeat all the entries in the UDF (and add/remove/change if the list changes) you could modify the UDF like this. My 'Removal List' starts in K2.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> Tidy(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> p <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    Application.Volatile<br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .IgnoreCase = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    p = "^(" & Join(Application.Transpose(Range("K2", _<br>        Range("K" & Rows.Count).End(xlUp))), "|") & ")"<br>    RegEx.Pattern = p<br>    Tidy = RegEx.Replace(s, "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

Excel Workbook
	F	G	H	I	J	K
1	toslcdtva	a				Removal List
2	samlcdtvzzzz	zzzz				toslcdtv
3	sanlcd					samlcdtv
4	abc shalcdtv	abc shalcdtv				sanlcd
5	hhhhshalcdc	hhhhshalcdc				shalcdtv
6						shalcd
7						PHILCD
8						CELLO
9						PHITFT
10						HANNSG
11						IIY
12						NECTFT
13						NECLCD
14						BTE
15						lgp
16						ppd
17						samp
18						pand
19						sam
20						peer
21						lox
22						sony
23						chief
24						bosch
25						onk
26						phi
27						hk
28						pan
29						lg
30
Sheet1

 

[Peter_SSs]

There is a further point/question. In the list that you gave, I note that you have "samlcdtv" and "sam". So if a cell contains "samlcdtvzzzz", how do we know what result you want returned? Is it "zzzz" (removing "samlcdtv") or is it "lcdtvzzzz" (removing "sam" only)?

With my UDF, the order of the items in the list determines the outcome. Refer to the following two screen shots where F1 is the same in each case but the green items in column K are reversed.

So, depending on your circumstances, further code modification may be required, or you may decide to carefully choose the order you place the items in the column (or in the code array if you are sticking with my earlier code).

Excel Workbook
	F	G	H	I	J	K
1	samlcdtvzzzz	zzzz				Removal List
2						toslcdtv
3						samlcdtv
4						sanlcd
5						pand
6						sam
7						peer
8
Sheet2

Excel Workbook
	F	G	H	I	J	K
1	samlcdtvzzzz	lcdtvzzzz				Removal List
2						toslcdtv
3						sam
4						sanlcd
5						pand
6						samlcdtv
7						peer
8
Sheet2