Substring a variable length field

[bae14]

Hi, I'm hoping for some help on how to substring a field using VB. I have fields such as B6002_1.9.6.15C O, 103456_304793. I need the #'s and digits both to the left and right of the underscore as separate fields (B6002 as one field and then 1.9.6.15CO as a second field). The underscore is never in the same position in the field. Can anyone please help? thanks

[OuTCasT]

Dim str as String
str = "B6002_1.9.6.15 CO"
str = str.replace("_" ,"#_#")

That will replace the _ with the #_# no matter where it will occur.

[Stewart Ross]

I think the poster wants to split the string into two components using the underscore as a separator:
'B6002_1.9.6.15 CO' >>> 'B6002' and '1.9.6.15CO',
'103456_304793' >>> '103456' and '304793'.

Dim intPos as Integer
Dim strFirstPart as String, strSecondPart as String
intPos = Instr(CombinedString, "_")
If intPos > 0 Then
    strFirstPart = Left(CombinedString, intPos - 1)
    strSecondPart = Mid(CombinedString, intPos + 1)
End IF

(Mid used without a length parameter returns everything from the given position to the end of the string.)

-Stewart

[bae14]

Thanks for all responses. Actually I got them split and then used the "replace" to remove the underscore. The replace command will really be useful. Thanks. And Stewart, I used a form of your Left and Mid to get the original breakout. I appreciate everyone's help!