Copying part of a Text String of different lengths

[Poundland]

Hi guys,

I wonder if you can help me, I need some code to copy part of a text string in a cell, usually I would simply use a MID formula, however each cell will have different data lengths, and example of it is below;

"400 (HOMEWARES)/403 (FOOD WRAP & STORAGE)/111 (FOOD STORAGE)/103 (MICROWAVE CONTAINERS)"

400 (HOMEWARES)/405 (DINING)/101 (TABLEWARE)/111 (MUGS/CUPS)

550 (CELEBRATIONS)/567 (TABLE & DRINKWARE)/101 (PARTYWARE)/103 (GLASSES)

In this example I only want to copy the text I have highlighted in Red from each cell, I then want to concatenate all the collected data into one cell.

Any ideas?

[Jan Karel Pieterse]

Like this:

<takes deeeep breath>

=LEFT(MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(")",MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)))-1)

<releases deep breath>

[GTO]

Duplicate thread, see: http://www.vbaexpress.com/forum/show...ferent-lengths

[Aussiebear]

Fixed it for you Mark.

[Aussiebear]

=LEFT(MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(")",MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)))-1)

<releases deep breath>

"Only one deep breath"... very impressive. but errr... lets see, 1 left, 6 lens, 6 Mids, 7 Finds and 44 Parenthesis's . Any chance you could explain it in under 100 words?

[Aussiebear]

=LEFT(MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(")",MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)))-1)

I'm guessing the "FIND("/",A1)" finds the first occurrence of the "/", while "(Mid(Find("/",A1)+1 finds the second occurrence of the"/"? and after that I'm lost

[Jan Karel Pieterse]

OK, I was expecting that, just didn't have time to split this into pieces.

Lets take this example:

400 (HOMEWARES)/403 (FOOD WRAP & STORAGE)/111 (FOOD STORAGE)/103 (MICROWAVE CONTAINERS)"

First step: find first / and get all text after that:

=MID(A1,FIND("/",A1)+1,LEN(A1))

Result:

403 (FOOD WRAP & STORAGE)/111 (FOOD STORAGE)/103 (MICROWAVE CONTAINERS)"

Step 2: find first (, get all text after that. because we need to find it in the string we got in step 1, we repeat that piece of the formula:

=MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1))

Result:

FOOD WRAP & STORAGE)/111 (FOOD STORAGE)/103 (MICROWAVE CONTAINERS)"

Now we need the part up to the first ). Again, we repeat the formula from the previous step:

=LEFT(MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(")",MID(MID(A1,FIND("/",A1)+1,LEN(A1)),FIND("(",MID(A1,FIND("/",A1)+1,LEN(A1)))+1,LEN(A1)))-1)

Clear as mud?

[Aussiebear]

Absolutely. Yep Clear as Mud,....


Just one question, are we talking European Mud or Aussie Mud here? European mud is eco friendly, no biomass, ultra low emissions and could be distilled as a rum base whilst aussie mud is something you could bog a low flying duck at 50 paces in.

[Jan Karel Pieterse]

Dutch mud of course.

[snb]

Some less mud:

=MID(A1;FIND("(";A1;6)+1;FIND(")/1";A1)-FIND("(";A1;6)-1)

or

=MID(LEFT(A15;FIND(")/1";A15));FIND("(";A15;6);100)

[Bob Phillips]

Another way, that doesn't depend on any structure in the first part

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),2)),FIND(CHAR(1),SUBSTITUTE( A1,")",CHAR(1),2))-FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),2))+1)

[Paul_Hossler]

I think JKP wins for the longest WS function so far this year

I could never keep that much straight in my head, so that's what drove me to VBA many (many, many, many) years ago.

Option Explicit
Sub drv()
    MsgBox SplitOut("400 (HOMEWARES)/403 (FOOD WRAP & STORAGE)/111 (FOOD STORAGE)/103 (MICROWAVE CONTAINERS)")
End Sub
 
Function SplitOut(s As String) As String
    Dim sTemp As String
    Dim vSplit As Variant
    Dim iParen As Long
    
    vSplit = Split(s, "/")
    
    sTemp = vSplit(1)
    
    iParen = InStr(sTemp, "(")
    
    SplitOut = Right(sTemp, Len(sTemp) - iParen + 1)
End Function

My preference is to be wordy, and not try to write everything into a single statement.

I figure the computer doesn't care, and I'm the person who will have to try and read it in 6-8 months

Paul

[snb]

or

Sub M_snb()
    MsgBox F_snb("400 (HOMEWARES)/403 (FOOD WRAP & STORAGE)/111 (FOOD STORAGE)/103 (MICROWAVE CONTAINERS)")
End Sub

Function F_snb(c00)
   F_snb = Split(Split(c00, "(")(2), ")")(0)
End Function

[Jan Karel Pieterse]

Hi Paul,

Thanks. When can I collect my award?
:-)
I don't like VBA UDF's much. Even though I agree they may be easier to understand and maintain, they are often cause of havoc in complex files. Slow recalcs and troublesome VBA debugging come to mind.

[Bob Phillips]

Hi Paul,

Thanks. When can I collect my award?
:-)

Whenever you are ready to travel over to the States at your own cost Jan Karel

I don't like VBA UDF's much. Even though I agree they may be easier to understand and maintain, they are often cause of havoc in complex files. Slow recalcs and troublesome VBA debugging come to mind.

Seconded. I especially hate it when you are f8 debugging and the UDF gets called ... thousands of times ... uugh!

[Jan Karel Pieterse]

a Control+shift+F8 does get you out of that state.

What's worse is when the UDF does not have any error handling and does cause a runtime error, your code just ends.

[Bob Phillips]

Yeah, there is always something you can do, but it still keeps happening.