Removing parts of a string based on position

[mcomp72]

I need to remove part of a string with VBA code. The string will never be the same, so I have to treat it as variable. I know how to determine the starting position and the ending position within the string that I want to delete, but can't figure out how to do it. I've searched via Google but there doesn't seem to be an easy way to do it. I assume I need to use the Left, Right, and Replace methods together somehow, but I can't figure out the correct way.

For instance, let's say I have this string: "This is my string. It is very nice. Now let's each lunch."

And I need to change it to: "This is my lunch."

The position within the string where I want the deletion to start is 12.

The position within the string where I want the deletion to end is 54.

How would I delete everything in the string between position 12 and 54?

 

[Rick Rothstein]

Try...

=REPLACE(A1,12,43,"")

Note: 43 comes from 54-12+1 (the +1 makes sure both end points are counted). For the general case, you can replace these numbers with the expressions that were used to calculate them.

 

[GWteB]

If you know what part to delete, there is no need to determine start and end position. You might use

    Dim s As String, d As String, Result As String

    s = "This is my string. It is very nice. Now let's each lunch."
    d = "string. It is very nice. Now let's each "

    Result = Replace(s, d, "")

 

[alansidman]

Will your workbook always start at 12 and end at 54? Not sure that is clear in your request.

 

[mcomp72]

Will your workbook always start at 12 and end at 54? Not sure that is clear in your request.

Nope, it will be different each time.

 

[mcomp72]

If you know what part to delete, there is no need to determine start and end position. You might use

    Dim s As String, d As String, Result As String

    s = "This is my string. It is very nice. Now let's each lunch."
    d = "string. It is very nice. Now let's each "

    Result = Replace(s, d, "")

What needs to be deleted will be different each time.

 

[mcomp72]

Try...

=REPLACE(A1,12,43,"")

Note: 43 comes from 54-12+1 (the +1 makes sure both end points are counted). For the general case, you can replace these numbers with the expressions that were used to calculate them.

Can you tell me how I would do it with VBA code? I'm trying to create a sub that will do it if I pass it the string, the first position, and the last position.

Here's what I have:

Sub RemovePartOfString(myString As String, FirstPos As Integer, LastPos As Integer)

Dim newString As String

Debug.Print "myString: " & myString
Debug.Print "First Position: " & FirstPos
Debug.Print "Last Position: " & LastPos

newString = Replace(myString, FirstPos, LastPos - FirstPos + 1, "")

Debug.Print newString

End Sub

I get a Type Mismatch error on this line:

newString = Replace(myString, FirstPos, LastPos - FirstPos + 1, "")

 

[mcomp72]

Can you tell me how I would do it with VBA code? I'm trying to create a sub that will do it if I pass it the string, the first position, and the last position.

Here's what I have:

Sub RemovePartOfString(myString As String, FirstPos As Integer, LastPos As Integer)

Dim newString As String

Debug.Print "myString: " & myString
Debug.Print "First Position: " & FirstPos
Debug.Print "Last Position: " & LastPos

newString = Replace(myString, FirstPos, LastPos - FirstPos + 1, "")

Debug.Print newString

End Sub

I get a Type Mismatch error on this line:

newString = Replace(myString, FirstPos, LastPos - FirstPos + 1, "")

Oh, nevermind. I forgot that I could just put in "WorksheetFunction." before "Replace". I tried that and it works. Thank you!