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

 

[Sektor]

Function Remove(Str As String) As String
    Dim i As Long
    Application.Volatile
    For i = 1 To Len(Str)
        If IsNumeric(Mid(Str, i, 1)) Then
            Remove = Right(Str, Len(Str) - i + 1)
            Exit Function
        End If
    Next
End Function

 

[nafri]

Function Remove(Str As String) As String
    Dim i As Long
    Application.Volatile
    For i = 1 To Len(Str)
        If IsNumeric(Mid(Str, i, 1)) Then
            Remove = Right(Str, Len(Str) - i + 1)
            Exit Function
        End If
    Next
End Function

Many thanks for your reply.But this is not what i need.I have a list of words, if any of these words match the beginging of any words in a given coloumn , then i want the word to be removed.Some words are two character long and and some are six or seven

for example

In list i have lg, pdc, lptr,romeop, juice, elven and so on.
Now if the beging characters in the first word in the coloumn match any of these words then i would like the word to be removed.Only in the begining. there will be a instance where the same word is repeated a number of times, on the same row but i only want it removed if it is found in the start.if the word is lg2458 then i would like only the lg to be removed not 2458 and so on.

hope this makes it more clearer.

 

[Sektor]

So, post those words!

 

[nafri]

So, post those words!

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

 

[Sektor]

Function Remove(Str As String) As String
    
    Dim IsFound As Boolean
    Dim i As Long, j As Long, arr As Variant
    Dim sRemove As String
    
    Application.Volatile
    
    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 = 1 To Len(Str)
        If IsNumeric(Mid(Str, i, 1)) Then
            sRemove = Left(Str, i - 1)
            For j = 0 To UBound(arr)
                If arr(j) = sRemove Then
                    IsFound = True
                    Exit For
                End If
            Next
            If IsFound Then
                Remove = Right(Str, Len(Str) - i + 1)
            Else
                Remove = Str
            End If
        End If
    Next
    
End Function

 

[nafri]

Thanks for your reply.. i ran the function. and it doesnt work. it is deleting some rows where the the word doesnt exist and also where the word does exist it is not removing them.i am unable to attach the file as attchment, so u can have a look at it yourself.

 

[Peter_SSs]

Try this UDF

<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> arr<br>    <SPAN style="color:#00007F">Dim</SPAN> p <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <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>        arr = Array("toslcdtv", "samlcdtv", "sanlcd", "shalcdtv", "shalcd", "PHILCD", "CELLO", "PHITFT", _<br>                "HANNSG", "IIY", "NECTFT", "NECLCD", "BTE", "lgp", "ppd", "samp", "pand", _<br>                "sam", "peer", "lox", "sony", "chief", "bosch", "onk", "phi", "hk", "pan", "lg")<br>        <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(arr) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(arr)<br>            p = p & "|" & arr(i)<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        p = "^(" & Replace(p, "|", "", 1, 1) & ")"<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>            .Pattern = p<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>    Tidy = RegEx.Replace(s, "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

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

 

[Ron Coderre]

Try this....

With
A1: containing text you want to evaluate...e.g. pandfgsswer pan
and
K1:K29 containing your list...PLUS an extra cell with a pipe character: |
(to avoid errors)
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
|

This regular formula removes the first instance of the first list word encountered in A1...

B1: =SUBSTITUTE(A1,INDEX($K$1:$K$29,MATCH(1,INDEX(--ISNUMBER(
SEARCH($K$1:$K$29,A1&"|")),0),0)),"",1)

In the above example, the formula returns: fgsswer pan

Is that something you can work with?

 

[Sektor]

I checked this out - all works as expected...