CODEWITHSUNDEEP
stringexcelvba
ikathegreat
ikathegreat

Reputation: 2321

Remove right half of string after a certain number of spaces

I'm writing a macro in Excel that is reading some text from a single cell.

ProductID = rw.Cells(1, 1).Text

However the cell may contain some buffer characters, specifically 5 consecutive space characters. I am trying to remove all the characters (length and actual text may vary) after the 5 spaces (including the spaces).

So if the string was:

MyProduct123     removethis

The desired string would be 

MyProduct123

It seems I can remove the 5 spaces with

Replace(MyProductStr, "     ", "")

but how can I get the position of the right side string or the text to remove that?

Upvotes: 0

Views: 438

Answers (1)

stuartd
stuartd

Reputation: 73253

You can do this using InStr to find the starting position of the five spaces, and then Left to take just the part of the string before that:

Dim pos As Integer

pos = InStr(ProductID, "     ")

If pos > 0 Then
   ProductID = Left(ProductID, pos - 1)
End If

Upvotes: 1

Related Questions