Need to parse and convert to a number

[Udir]

I have a string that is in the form of:

Garbage(-338409,-110472,8275)

Garbage(-336179,-111388,4469)

I want to sort the data out into 3 seperate columns and use them as numbers so I can use them for calculations.

How do I best go about that?

 

[PHV]

A starting point:
Dim myStr As String, myArrNum
myStr = "Garbage(-338409,-110472,8275)"
myNumArr = Split(Replace(Mid(myStr, InStrRev(myStr, "(") + 1), ")", ""), ",")
MsgBox "col1=" & myNumArr(0) & ",col2=" & myNumArr(1) & ",col3=" & myNumArr(2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

There may be a better way, but you could use something like this ...

Sub zzzzzzzzzzzzz()

    Dim strTest As String, a As Long, arrStr() As String, arrLng() As Long
    
    strTest = "Garbage(-338409,-110472,8275)"
    a = InStr(1, strTest, "(")
    If a <> 0 Then
        strTest = Right(strTest, Len(strTest) - a)
    End If
    a = InStr(1, strTest, ")")
    If a <> 0 Then
        strTest = Left(strTest, a - 1)
    End If
    arrStr = Split(strTest, ",")
    arrLng = ConvertArray_Long(arrStr())
    
End Sub

Function ConvertArray_Long(arrVar)
    Dim i As Long, arrTmp() As Long
    ReDim arrTmp(LBound(arrVar) To UBound(arrVar))
    For i = LBound(arrVar) To UBound(arrVar)
        arrTmp(i) = Val(arrVar(i))
    Next i
    ConvertArray_Long = arrTmp()
End Function

-----------
Regards,
Zack Barresse

 

[firefytr]

Sure, I'd explicitly reference it as far as you need to go though...

myStr = Workbooks("Book1.xls").Sheets("Sheet1").Range("A3").Value

-----------
Regards,
Zack Barresse

 

[Udir]

Well that is what I wanted to accomplish, but I just want to do it automatically when I copy a bunch of data into the worksheet.

That information is actually in column 2 but only like every 10 rows, but that is subject to change.

 

[PHV]

Udir, tons of threads here showing you how to travel down the rows !
What have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

ALTHOUGH this is the VBA forum...

This can be done on the worksheet using Data/Text to columns... in 3 easy steps
[tt]
1. Delimit on (
2. Delimit on )
3. Delimit on ,
[/tt]
macro record it, if this is a recurring task.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[firefytr]

What exactly are you trying to accomplish here? What is the desired end result? More details are needed really.

-----------
Regards,
Zack Barresse

 

[Udir]

Ok, in excel I want to paste some data that will have an unknown set of information in the paste from another program.

The knowns are that the information that I am wanting to use is contained in the string that appears in column 2 within random rows within the worksheet. When I paste the data into the worksheet, it starts at A4 and runs however big the data pasted is.

I want Garbage(-336179,-111388,4469) to look like:

Column 1 2 3 4 5
Garbage -336179 -111388 4469


From there I want to run a calcuation in column 6 using information from a known the user keys in.

 

[firefytr]

What calculation? What other data will be in column 2? Will there be data in columns 3-5 before your numbers? A little more details please.

-----------
Regards,
Zack Barresse

 

[Udir]

Column in the first case will contain the "random string (-336179, -111388, 4469).

Some number of rows in between that and then next grouping of data that I want to look at, is two columns of garbage. Usually 15 rows in between.

There is currently nothing in Columns 3-5 yet, but I would like -336179, -111388, 4469 in each of the columns.

The user in A2 keys in an X value, A3 keys in Y value, and A4 keys in a Z value. From there I want to in columns 6 next to Cell 3-5 that contain the data, run a calculation based on the user input against the data just sorted out.

 

[firefytr]

Let me get this straight..


A2, A3 & A4 = User input values.

Starting at B4 and going down is your variable text strings.

Enter - where applicable - numbers from text string to C4:E4 (and down rows as needed).

Put a calculation in F4 (and down rows as needed).


Sound right? Also, you never told us the calculation you want to perform..

-----------
Regards,
Zack Barresse

 

[firefytr]

Also, are there actually spaces after the commas? First example you showed did not, second one did. Is it variable?

-----------
Regards,
Zack Barresse

 

[PHV]

A more elaborated starting point (VBA code of course):
Sub myParseNum()
Dim c As Range, myStr As String, myArrNum, i As Integer
For Each c In ActiveWorkbook.ActiveSheet.Range("B:B")
myStr = Trim(c.Text)
If myStr Like "*(*,*)" Then
myArrNum = Split(Replace(Mid(myStr, InStrRev(myStr, "(") + 1), ")", ""), ",")
For i = 0 To UBound(myArrNum)
c.Offset(0, 1 + i) = Val(myArrNum(i))
Next
End If
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Udir]

Ok let me fix this.

C2, D2, E2 contain the user variables x1, y1, z1

The data that I want to use starts in B4 and runs throughout the B's. I want the numeric part to appear in C, D, E. x2, y2, z2.

in the same row as the information is extracted appears (c-e) in column h of I want run this calcuation:

a = x1-x2

b = y1-y2

c = z1-z2

dt = sqrt(a + b + c)

 

[Udir]

Yes there are spaces, and yes it is a variable... Never the same number. That was just me keying it wrong.

 

[firefytr]

Well, mine might be longer than PHVs, but it works ...

Sub EnterValuesAndCalcsPlease()
    Dim c As Range, rngLoop As Range
    Dim strTest As String, a As Long, n As Long
    Dim arrStr() As String, arrLng() As Long
    Call SpeedUp
    On Error GoTo ErrEnd
    Set rngLoop = Range("B4", Cells(Rows.Count, 2).End(xlUp))
    For Each c In rngLoop
        strTest = c.Value
        If strTest Like "*(*,*)" Then
            arrStr = Split(Replace(Mid(strTest, InStrRev(strTest, "(") + 1), ")", ""), ",")
            arrLng = ConvertArray_Long(arrStr())
            Range("C" & c.Row).Value = arrLng(0)
            Range("D" & c.Row).Value = arrLng(1)
            Range("E" & c.Row).Value = arrLng(2)
            Range("H" & c.Row).Formula = "=SQRT(($C$2-C" & c.Row & ")+($D$2-D" & c.Row & ")+($E$2-E" & c.Row & "))"
        End If
    Next c
ErrEnd:
    Call SpeedDown
End Sub

Function ConvertArray_Long(arrVar)
    Dim i As Long, arrTmp() As Long
    ReDim arrTmp(LBound(arrVar) To UBound(arrVar))
    For i = LBound(arrVar) To UBound(arrVar)
        arrTmp(i) = Val(arrVar(i))
    Next i
    ConvertArray_Long = arrTmp()
End Function

Sub SpeedUp()
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub

Sub SpeedDown()
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
End Sub

Does not take into account any negatively calculated value for finding the square root of, so you would end up with a #NUM error on those (if applicable).

-----------
Regards,
Zack Barresse

 

[PHV]

Well, mine might be longer than PHVs, but it works ...
Mine didn't work ???

 

[Udir]

It worked great... Thanks for your help!!!

 

[firefytr]

PHV: nope, didn't say that and wasn't implying that. Was only inferring to my limited skill set. In fact, if you'll notice, I used some of your coding to shorten it up a bit.

-----------
Regards,
Zack Barresse