get part of a string according to delimeter

[cpampas]

Hello,
I am trying to retrieve from the following string the numeric part into 3 different variables

str="7449_9968_12438"

i was able to get first and last set of characters
var1=Left(str, InStr(str, "_") - 1)
var2=Right(str, InStr(str, "_"))

but i cant figure out how to get the middle string "9968" ( the lenght of this part of the string can vary)

 

[jdraw]

Is the format consistent?

Mid(str,1,4) ==>7449
Mid(str,6,4) ==>9968
Mid(str,11,5) ==>12438

 

[cpampas]

no, the format is not consistent, the length of each set of numeric characters can go from1 to 6

 

[Gasman]

I would just use the Split() function?

 

[jdraw]

Agree with Gasman----Split().

 

[cpampas]

i will try split and let you know
thanks for helping

 

[CJ_London]

but i cant figure out how to get the middle string "9968" ( the lenght of this part of the string can vary)

another way perhaps if you want numeric values rather than strings

var1=val(str)
?val("7449_9968_12438")
7449

var2=val(mid(str, InStrRev(str, "_")+1))
?val(mid("7449_9968_12438",instrrev("7449_9968_12438","_")+1))
12438

var3=val(mid(str, InStr(str, "_")+1))
?val(mid("7449_9968_12438",instr("7449_9968_12438","_")+1))
9968

edit #1:

note that this

var2=Right(str, InStr(str, "_"))

only works because the first number has 4 characters and the last number has 5

edit #2:

to use the split function, assuming you are in vba

var1=split(str,"_")(0)
var2=split(str,"_")(2)
var3=split(str,"_")(1)

but these will be strings

 

[cpampas]

thank you guys, the split function works like a charm