Need VBA help to copy a portion of a text string...

[Lidsavr]

I run Excel 2007 and write code that Excel 2003 users will run.

I want to be able copy all of the right characters of a text string up to the @ symbol and place them in a variable. The problem I am encountering is that the text strings are different sizes and sometimes there are anywhere between one and seven characters to the right of the of the @ symbol. Here is an example of one of the text strings:

CAM-20016W @AT_G04

In this case, I want to place AT_G04 (Total of six characters) in a variable and recall it later.

Another example is:

GNAM-GB010W @F_D04

F_D04 has a total of five characters.

I cannot find any reference material that would tell me how to code this.

Can anyone help me with this?

Thanks,

Charles

 

[baitmaster]

This should help you in what you are after. You will need error handling for when no "@" is found. Change "selection" as you require, and obviously get rid of the msgbox..

MsgBox Right(Selection, Len(Selection) - WorksheetFunction.Find("@", Selection))

 

[shg]

Another way:

Function RightOfAt(sInp As String) As String
    RightOfAt = Mid(sInp, InStr(sInp, "@") + 1)
End Function

 

[pgc01]

... and another:

Function RightOfAt(sInp As String) As String
    RightOfAt = Split(sInp, "@")(1)
End Function

 

[shg]

I think that breaks if there is no "@", pgc.

 

[pgc01]

I think that breaks if there is no "@", pgc.

You are right, shg. I assumed the string has a "@".
If it has not maybe it should return an empty string?

Like:

Function RightOfAt1(sInp As String) As String
    If InStr(sInp, "@") Then RightOfAt1 = Split(sInp, "@")(1)
End Function

Btw, your function should perhaps do the same test? If the string does not have the "@" maybe is better that the function returns the empty string, instead of the input string.

 

[shg]

You're right, the OP may want a null string if there's no @ rather than the whole string. Returning the whole string probably makes more sense for the "LeftOfAt" function.

 

[pgc01]

... and a simpler version of my function:

Function RightOfAt(sInp As String) As String
    RightOfAt = Split(sInp & "@", "@")(1)
End Function

 

[Lidsavr]

pgc01, shg and baitmaster,

Thank you for your help! I am sure that one of the examples you gave me will work. I could not see in my mind how to construct the code. You have provided me with much help.

By the way, I did not mention in my original post that the text string I will be working with always has a "@" preceeding the portion of text that I want to copy.

Thank you again!

Charles