Split String Question

[bigshop]

I get strings of text like this:
1.1-2.1-3.1

Im looping thru a string of numbers and rounding the numbers up to a full number
That bit works ok
How can I put the result back togeather like this:
2-3-4

Here is what I have so far:

Dim myString As String
Dim Result As String
Dim MyArr As Variant, ArrItem As Variant
myString = "1.1-2.1-3.1"

MyArr = Split(myString, "-")

For Each ArrItem In MyArr
Result = Int(ArrItem) + 1
Debug.Print Result
Next ArrItem

 

[GaP42]

Untested:

Dim myString As String
Dim Result As String
Dim MyArr As Variant, ArrItem As Variant
MyNewString as string

myString = "1.1-2.1-3.1"
myNewstring = ""

MyArr = Split(myString, "-")

For Each ArrItem In MyArr
   Result = Int(ArrItem) + 1
   Debug.Print Result
   MyNewString = MyNewString & "-" & Result
Next ArrItem

If your input is 1.0-2.1-3.1 do you still want the first number to be rounded up?

 

[bigshop]

Untested:

Dim myString As String
Dim Result As String
Dim MyArr As Variant, ArrItem As Variant
MyNewString as string

myString = "1.1-2.1-3.1"
myNewstring = ""

MyArr = Split(myString, "-")

For Each ArrItem In MyArr
   Result = Int(ArrItem) + 1
   Debug.Print Result
   MyNewString = MyNewString & "-" & Result
Next ArrItem

If your input is 1.0-2.1-3.1 do you still want the first number to be rounded up?

No i dont want 1.0 to be rounded up
Actually i had not tested full numbers yet
I notice it rounds up all full numbers to the next digit

 

[theDBguy]

No i dont want 1.0 to be rounded up
Actually i had not tested full numbers yet
I notice it rounds up all full numbers to the next digit

Are you trying to just drop the decimal part without rounding up the whole number part? For example, 1.6 becomes 1 and not 2?

 

[bigshop]

Are you trying to just drop the decimal part without rounding up the whole number part? For example, 1.6 becomes 1 and not 2?

What i need is all not even numbers round up
1.0 = 1
2.1 = 3
3.1 = 4
Etc

 

[GaP42]

So you will need to check the string after the splitting, as the first step in the for loop, to know whether to add 1 or not.
(IF ArrItem Value after "." > 0 then Result = Int(ArrItem) + 1 ELSE Result = .Int(ArrItem))

Are your numbers always one decimal place? If so then just check for "0" after the "." (use Instr and Mid functions).
If they vary in length then perhaps using a conversion to number and modulus operation to see if all values after the decimal point are 0.

 

[KitaYama]

(IF ArrItem Value after "." > 0 then Result = Int(ArrItem) + 1 ELSE Result = .Int(ArrItem))

That's too much of a hustle.

This one is simpler. Because the data type is variant, VBA works on it just like numbers.
It also takes care of whole numbers and doesn't round them up. (OP's request in #3)

Result = -Int(-ArrItem)

By the way, your code in #2, gives an additional "-" at the first of final result.
You must get ride of it before ending the function:

MyNewString = mid(MynewString,2)

 

[KitaYama]

@bigshop test this:

Sub Test()
    Dim myString As String
    Dim MyArr As Variant, ArrItem As Variant
    Dim MyNewString As String

    myString = "1.1-2.1-3.1"
    MyArr = Split(myString, "-")

    For Each ArrItem In MyArr
       MyNewString = MyNewString & "-" & -Int(-ArrItem)
    Next ArrItem

    MyNewString = Mid(MyNewString, 2)
    Debug.Print MyNewString
End Sub

 

[bigshop]

That's too much of a hustle.

This one is simpler. Because the data type is variant, VBA works on it just like numbers.
It also takes care of whole numbers and doesn't round them up. (OP's request in #3)

Result = -Int(-ArrItem)

By the way, your code in #2, gives an additional "-" at the first of final result.
You must get ride of it before ending the function:

MyNewString = mid(MynewString,2)

I had noticed that and was getting around to figuring it out but you have beaten me to it

 

[bigshop]

@bigshop test this:

Sub Test()
    Dim myString As String
    Dim MyArr As Variant, ArrItem As Variant
    Dim MyNewString As String

    myString = "1.1-2.1-3.1"
    MyArr = Split(myString, "-")

    For Each ArrItem In MyArr
       MyNewString = MyNewString & "-" & -Int(-ArrItem)
    Next ArrItem

    MyNewString = Mid(MyNewString, 2)
    Debug.Print MyNewString
End Sub

This code works prefect, also gets rid of the "-" at the beginning of the results
I cant think of anything to improve, works juts how i wanted
Thanks

 

[MarkK]

There is also the VBA.Join() function, which does the opposite of VBA.Split()...

Sub Test2()
    Dim var
    Dim i As Integer

    var = Split("1.1-2.1-3.1", "-")

    For i = 0 To UBound(var)
        var(i) = -Int(-var(i))
    Next

    Debug.Print Join(var, "-")

End Sub