Help extracting a part of a string 3

[vestax22]

Hi, using excel 2000 VBA.

I have this string :

2003071-SI

I would like to isolate 2003071

The problem is that this string could vary. It coulb be the following :

03071-SI

so what I would like to do is extract the "-SI" and store the remainder of the string. Hope this is clear enough.

Thx

 

[Clarkie001]

If you no longer require the "-SI" part, you could replace it with ""
Create a new function in a blank module called Change.
Use this code:
Change = Replace(FieldName,"-SI","&quot

Run the function through a query.

Hope this helps any.

John

 

[JohnAcc]

Hi There,

Try something like this....

Sub test()
Text = "2003071-SI"
minussign = InStr(1, Text, "-&quot
NewString = Mid(Text, 1, minussign - 1)
MsgBox NewString
End Sub


Let me know how you get on.

Cheers

John

 

[vestax22]

Hey, I stored the original string inside a string variable. Now I want to seperate this variable into two distict variables. One containing 2003071 and the other containing S1. Like I said above the 2003071 could vary so I cannot use the left function to extract it. I could use the right function to extract the S1 part though but I need both. Any suggestions?

Thx

 

[Molby]

As an FYI, if you don't want to use code, you can use the substitute formula:

=SUBSTITUTE(A1,"-SI","&quot in another column, this will do the same thing.

Regards,

Ian

 

[danomaniac]

Or how about...

Sub test()

dim teststring1 as string
dim teststring2 as string
dim x as integer

teststring1 = "2003071-SI"

for x = 1 to len(teststring1)
if mid(teststring1,x,1) <> &quot;-&quot; then
teststring2 = teststring2 & mid(teststring1,x,1)
else
exit for
end if
next x
Exit Sub


&quot;It's more like it is now, than it ever has been.&quot;

 

[danomaniac]

in that case, try this...

Sub test1()
Dim teststring As String
Dim teststring2 As String
Dim teststring3 As String
Dim x As Integer

teststring = &quot;2003071-SI&quot;

'get first variable
x = 1

Do Until Mid(teststring, x, 1) = &quot;-&quot;
teststring1 = teststring1 & Mid(teststring, x, 1)
x = x + 1
Loop

'get second variable
x = x + 1

Do Until x = Len(teststring) + 1
teststring2 = teststring2 & Mid(teststring, x, 1)
x = x + 1
Loop

End Sub


Hope that helps...

Dan

&quot;It's more like it is now, than it ever has been.&quot;

 

[Bowers74]

Or you could do this:

Sub SplitString()
Dim Text1 As String
Dim Return1 As String
Dim Return2 As String
Text1 = "2003071-SI" ' or a cell reference
Return1 = WorksheetFunction.Substitute(Text1, "-SI", "")
Return2 = Right(Text1, 2)
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[danomaniac]

Oops...variable misnamed...

Try this

Sub test1()
Dim teststring As String
Dim teststring1 As String
Dim teststring2 As String
Dim x As Integer

teststring = &quot;2003071-SI&quot;

'get first variable
x = 1

Do Until Mid(teststring, x, 1) = &quot;-&quot;
teststring1 = teststring1 & Mid(teststring, x, 1)
x = x + 1
Loop

'get second variable
x = x + 1

Do Until x = Len(teststring) + 1
teststring2 = teststring2 & Mid(teststring, x, 1)
x = x + 1
Loop

End Sub


&quot;It's more like it is now, than it ever has been.&quot;

 

[TonyJollans]

Hi vestax22,

If your string is always separated by a hyphen (as everybody is assuming then why not use Split?

myArray = Split([A1], "-")

' Or Split(

StringVariableName, "-"

)

MsgBox myArray(0)

' Contains the number on the left

MsgBox myArray(1)

' Contains S1 or whatever is on the right

Enjoy,
Tony

 

[danomaniac]

Nice job Tony! I never knew there was a function that would do that.

Cool

&quot;It's more like it is now, than it ever has been.&quot;

 

[GoDawgs]

Too much code:

Left(TheString,len(TheString)-3)
and
Right(TheString,2) (or 3...didn't know if you want the dash or not)

that's only if the &quot;-S1&quot; portion is static...

 

[Bowers74]

Actually Dawgs,

Tony's code is shorter, it just &quot;looks&quot; longer because of the two MessageBoxes.

Nice Try ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[GoDawgs]

Ah, you're right, didn't notice that one. Nice work Tony.

 

[TonyJollans]

Thanks All,

Split and Join are really useful new functions in Office 2K.

Enjoy,
Tony