CODEWITHSUNDEEP
excelvba
user20114520
user20114520

Reputation:

VBA replace all text after specific word with ""

I have this string of text Part Number 1234 from workbook 2 and I want to save the string to be just Part Number 1234 and replace everything that occurs starting at from and to the right to be replaced with a space.

This is what I have tried so far. The way I have it set up now, it will replace "bin" with a space "" but if I change it to "*bin" it will not replace anything in the string.

Dim module As String, ws As Worksheet
Set ws = ActiveSheet 
module = ws.Cells(1, 2).value ' the cell value is Part Number 1234 from workbook 2 
    module = Replace(module, "bin", "")
    Debug.Print module

Upvotes: 0

Views: 571

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Your text states "replace "bin" with a space "", but "" is NOT a space.

If you want to replace everything from from and to the right with a null string, then:

Trim(Split(myString,deLimiter)(0))

or

Split(myString," " & deLimiter)(0)

If you really do want a space, then remove the Trim function from the first example, or the " " from the second.

Upvotes: 0

Related Questions