String extraction help.

[AncientOne]

Everyone,<o></o>
<o> </o>
I am attempting to extract and concatenate the following type string, extracting only those items not in parenthesis and excluding the /.<o></o>
<o> </o>
Here is an example of the string:
abcd, efgh, ijkl (ok) / mno, pqr (need_help!)<o></o>

The string is not static in length, but is static in format regarding the manner in which / and parenthesis are used.<o></o>
<o> </o>
I am easily able to retrieve everything to the left of the (ok) but I am having difficulties retrieving the “mno, pqr” out of the string and concatenating it without the follow-on (need_help!).

Example of what I have thus far and block where I’m stuck:</SPAN>

 .Cells(1, c).Value = Left(var, InStr(var, "(") - 1)      '& vbCrLf & Right(var, InStrRev(var, " / ") - 1)</SPAN>

I have attempted using multiple variations of Right and InStrRev along with the Len(var) - but I am just failing.</SPAN>

Can any of you mathematical Excel geniuses help me? I’m just stumped.</SPAN>

Sincerely,</SPAN>
AO </SPAN>

 

[MickG]

Perhaps this:-
Data in "A1" Results in "B1".

[COLOR="Navy"]Sub[/COLOR] MG26May54
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Sp = Split(Range("A1"), " ")
 [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR="Navy"]If[/COLOR] InStr(Sp(n), "/") = 0 And InStr(Sp(n), "(") = 0 [COLOR="Navy"]Then[/COLOR]
        Sp(n) = Replace(Sp(n), ",", "")
        Str = Str & ", " & Sp(n)
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] n
Range("B1").Value = Mid(Str, 2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AncientOne]

Mick,

I am already in a for each i in MyRange loop - With MyRange2 and this is the 3rd ElseIf. There are additonal formats that I have figured out.

I went ahead and declared the n and the Sp using:

Sp = (Split(Range(i), ""))

then your for loop.

It bombs at the Sp - (Spllit(Range(i), "")) stating that Range of Object Global Failed. Everything is a-ok and as expected in the Locals window and I am not utilizing any globals in this sub so I'm still stumped :/

 

[MickG]

If you have a column of these strings then you need to loop through each cell with the range with my loop inside, this outer loop :- perhaps:-

[COLOR="Navy"]Sub[/COLOR] MG26May12
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn, " ")
 [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR="Navy"]If[/COLOR] InStr(Sp(n), "/") = 0 And InStr(Sp(n), "(") = 0 [COLOR="Navy"]Then[/COLOR]
        Sp(n) = Replace(Sp(n), ",", "")
        Str = Str & ", " & Sp(n)
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] n
Dn.Offset(, 1).Value = Mid(Str, 2): Str = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AncientOne]

Yes Mick,

I am already looping through a row with 2 ranges identified, startcol and endcol variables as well as c representing the column in the above .cells(1, c) by the time I get to this ElseIf.

I will play around somemore. You are leading me into the right direction I just need to get what I need.

Thank you
=)
AO

 

[MickG]

You're welcome