Using InStr to select string to copy

[tcarter963]

I have some text in cell C2 that looks like this: clonidine_Area
I want to copy the text before the underscore and paste it to another cell.

I've gotten this far but don't know how to proceed... How do I use the position number to selectively copy the string that I want? Any help would be greatly appreciated.


Sub Cp()

Dim SearchString, SearchChar, MyPos

SearchString = ActiveSheet.Range("C2")
SearchChar = "_"

MyPos = InStr(1, SearchString, SearchChar, 1)



End Sub

 

[Peter_SSs]

Something like below. Note that it is good practice to 'Dim' your variables as specific data types where possible, rather than leaving them all as Variant type.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Cp()<br>    <SPAN style="color:#00007F">Dim</SPAN> SearchString <SPAN style="color:#00007F">As</SPAN> String, SearchChar <SPAN style="color:#00007F">As</SPAN> String<br>    <SPAN style="color:#00007F">Dim</SPAN> MyPos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    SearchString = ActiveSheet.Range("C2")<br>    SearchChar = "_"<br>    <br>    MyPos = InStr(1, SearchString, SearchChar, 1)<br>    Range("D2").Value = Left(SearchString, MyPos - 1)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[tcarter963]

Thanks a bunch.

I appreciate the advice as well.