Extract text using LEFT/FIND but with VBA

[Thebatfink]

Hi,

I'm currently using the formula below to extract partial strings from cell contents for example,

=LEFT(J11,FIND("*",J11,1)-1)

I'd like to do this with VBA however. I have tried,

Application.WorksheetFunction.Left(myString, _
Application.WorksheetFunction.Find("*", myString, 1) - 1)

but it didn't like it. I presume this is possible, but what is the correct way of doing this?

Thanks!
Batfink

 

[Momentman]

I guess you could do it in one line

left(mystring, worksheetfunction.find("*",mystring,1)-1)

Hope it works

 

[Rick Rothstein]

Hi,

I'm currently using the formula below to extract partial strings from cell contents for example,

=LEFT(J11,FIND("*",J11,1)-1)

I'd like to do this with VBA however. I have tried,

Application.WorksheetFunction.Left(myString, _
Application.WorksheetFunction.Find("*", myString, 1) - 1)

but it didn't like it. I presume this is possible, but what is the correct way of doing this?

I would do it this way in VBA...

ModifiedText = Split(Range("J11").Value, "*")(0)

You didn't say where you wanted the output to go, so I simply assigned it to a variable. The part of the right side of the equal sign is the key part.... it can be assigned to any cell value including J11 (that is, change the contents of J11 directly)>

 

[Thebatfink]

I guess you could do it in one line

left(mystring, worksheetfunction.find("*",mystring,1)-1)

Hope it works

It is one line, I underscored it to stop it having scroll bars in the post.

Rick,

Will that give me all the text to the left of the * in the string? What about to the right of the *? I will populate a variable with the output, but I am also using mystring as the input variable as I already have this string populated earlier in my code.

I'm guessing it is possible to find a * judging from the replies, I was worried it may use it as a wildcard rather than a charactor to find.

Thanks

 

[Rick Rothstein]

Rick,

Will that give me all the text to the left of the * in the string? What about to the right of the *? I will populate a variable with the output, but I am also using mystring as the input variable as I already have this string populated earlier in my code.

I'm guessing it is possible to find a * judging from the replies, I was worried it may use it as a wildcard rather than a charactor to find.

The code I posted will return what you asked for... all the text to the left of an asterisk (if there is no asterisk, it returns the original text). The asterisk will not be considered a wildcard (the Split function does not recognize wildcards). You can use your mystring variable in place of where I posted Range("J11").

 

[Thebatfink]

Hi, thanks for the help!! For others information I achieved this using the code below -

Dim splitString as String
Dim splitStringLeft as String
Dim splitStringRight as String

splitString = "111111-111*1"

'split string to the left of *

splitStringLeft = Split(text, "*")(0)

'split string to the right of *

splitStringRight = Split(text, "*")(1)

 

[Rick Rothstein]

Hi, thanks for the help!! For others information I achieved this using the code below -

'split string to the right of *
 
splitStringRight = Split(text, "*")(1)

If there could be a second asterisk in the text and you wanted everything past the first asterisk, then the above will not give you that (it will only give you the text between the two asterisks). Change your code to this and if will work for one or more asterisks...

splitStringRight = Split(text, "*", 2)(1)

 

[Norie]

Why not just use Instr?

Dim splitString As String
Dim splitStringLeft As String
Dim splitStringRight As String
Dim pos As Long
 
splitString = "111111-111*1"
 
pos = InStr(splitString, "*")

splitStringLeft = Left(splitString, pos - 1)
splitStringRight = Mid(splitString, pos + 1)

 

[Thebatfink]

Both I guess valid options! I do have a query regarding the "Split" function.. How would I trap the error that occurs if the deliminating character is not present ie, there is no "*" in the string?
Is it possible with an IF statement?

Thanks!

 

[Norie]

You could check if the delimiting character exists in the string by using InStr before you use Split.

Or you could try this.

Dim splitString As String
Dim splitStringLeft As String
Dim splitStringRight As String
Dim pos As Long
 
splitString = "111111-111*1"
 
pos = InStr(splitString, "*")
 
 
If pos <> 0 Then ' check delimiter is found
   splitStringLeft = Left(splitString, pos - 1)
   splitStringRight = Mid(splitString, pos + 1)
End If