Redim Preserve?

[silentwolf]

Hi guys,

I am struggling again gg../

Function MandatLoeschen(ByVal strText As String) As String
    Dim strSearch As String
    Dim varWoerter As Variant
    Dim lngCounter As Long

    
    Dim strMandat As String
    
    strSearch = "Mandatsnummer:"
    
    varWoerter = Split(strText, " ")
    
    For lngCounter = LBound(varWoerter) To UBound(varWoerter)
        If varWoerter(lngCounter) = strSearch Then
            MandatLoeschen = varWoerter(lngCounter) & " " & varWoerter(lngCounter + 1)
            
            
            MandatLoeschen = Replace(strText, MandatLoeschen, "")
            
        Else
     End If
    Next lngCounter
End Function

Following code works if I am not using it within my code..
When I run it from another code it delets the remaining records ..(
So I guess there need to be a redim preserve in it .. but in my case how does this one need to be??

Many thanks as always

 

[silentwolf]

HI shg,

Well it is a UDF Gg .) but I just did not think of an Exit For ...

Function MandatLoeschen(ByVal strText As String) As String
    Dim strSearch As String
    Dim varWoerter As Variant
    Dim lngCounter As Long

    Dim strMandat As String
    
    strSearch = "Mandatsnummer:"
    
    varWoerter = Split(strText, " ")
    
    For lngCounter = LBound(varWoerter) To UBound(varWoerter)
        If varWoerter(lngCounter) = strSearch Then
            MandatLoeschen = varWoerter(lngCounter) & " " & varWoerter(lngCounter + 1)
            MandatLoeschen = Replace(strText, MandatLoeschen, "")
    Exit For
    Else
        MandatLoeschen = strText
    End If

    Next lngCounter
End Function

Now it works as it supose to .) oh Mannnnnnnnnnnnnnyyyyyyyyy thanks gg

Tried so many different ways .. ggg oh dear time to go to bed I guess gg .

 

[silentwolf]

One question remains...

if you like to delete all of the remaining word after the word has been found..

MandatLoeschen = varWoerter(lngCounter) & " " & varWoerter(lngCounter + 1)

instead of this code above to have something
like

varWoerter(lbound(lngCounter) to varWoerter(ubound(varWoerter)

??

would be a quick way of getting rid of the remaining words if there are all been processed.

 

[shg]

It would help if you provided a clear example of input and desired results. I'm not going to guess.

 

[silentwolf]

Hi thanks,
It would not be a set of exact data I need.. and in fact it does not matter what data it is... "Its about words and what words need to be kept and what not"

"Huber Pty.Ltd 12 Milller Street New York Mandatsnumber: 123456789 somemore Referenzpoints In the text"
"Huber Pty Ltd" should be the outcome but not always that is what I mean ,,, so if in this instants I like to start form the varWord(3)="12" till the last word in the text in this case text"
so get rid of all from"varWord(3) to varWord(uBound) in other cases it my start at varWord(10) and only to varWord(12)
So in my UDF I have a strSearch... string varible where I look for a word .. before it was the Mandatsnumber... so I look for a word in the text if it is found I like to either get rid of it or keep it or get rid of all other words..

By the way I am not sure how I can insert a excel sheet or anything like that here...

So it is not about any examples of what outcome or what data it is. All that matters is to be able to say from word(0) to word(3) should be kept and the rest goes.. or other situations ..
I look for a word which is the starting point so to say and then decide what I need and what not.

Hope it is a bit clearer?

    For lngCounter = LBound(varWoerter) To UBound(varWoerter)
        If varWoerter(lngCounter) = strSearch Then
'            strOut = varWoerter(lngCounter) & " " & varWoerter(lngCounter + 1)
            strOut = varWoerter(lngCounter + 1) & " " & varWoerter(UBound(varWoerter))
            BisZuGmbH = Replace(strText, strOut, "")
    Exit For

this is what I have tried so far...

 

[silentwolf]

So if I got a text with companies with for example "Huber Industries Pty. Ltd 12 Miller Street New York Some More Referenzes noone really needs and they need to be out of the text but noone knows how many words are in the given Text"
This is not even the length of my string.. but if I need or like just "Huber Industries Pty. Ltd" and in another text it could be on a different location "Some stupid text in front of the Huber Industries Pty. Ltd then I need to find it just by searching"
For the Pty and then I like the return to be "Huber Industries Pty. Ltd" regardless where it would stand in the text.

Hope this is now better to understand?

 

[shg]

Here's what I mean by an example:

	A​	B​
1​	Input​	Output​
2​	some input string	output for that input
3​	another input string	and for that one
4​	and a third	and that one

... supported by any explanation necessary to connect the dots.

 

[silentwolf]

I thought I wrote some examples before on the worksheet Huber IT Delivery Pty. Ltd	Huber IT Delivery Pty. Ltd
Gerhard Müller GmbH REF: 5814252 1963524 Reference: Of some kind	Gerhard Müller GmbH
This is 0004152 / ( 01 / 54 ) get this data out from somewhere/ Erika Summersoon, 19 Springfield	Erika Summerspoon
X124 kjkjiu AU14575 Ref: Oh funny Data that is Make Night to day 15, 5487 What a Night 35 and what	Make Night to day

<tbody>
</tbody>

<tbody>
</tbody>

the tex would be much longer then this examples above so I am not sure if it would be a good idea to write those in here.

 

[shg]

Does the workbook contain a list of the phrases to be extracted?

How does this dovetail with the prior posts about looking for a specific word and the single word that follows?