View Full Version : Copying part of a Text String of different lengths

Poundland

01-14-2014, 07:06 AM

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

01-14-2014, 07:34 AM

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>

Duplicate thread, see: http://www.vbaexpress.com/forum/showthread.php?48664-Copying-part-of-a-Text-String-of-different-lengths

Aussiebear

01-15-2014, 04:20 AM

Fixed it for you Mark.

Aussiebear

01-15-2014, 04:29 AM

=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. :wot 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

01-15-2014, 04:41 AM

=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

01-16-2014, 01:28 AM

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

01-16-2014, 02:44 AM

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

01-17-2014, 08:29 AM

Dutch mud of course.

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)

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

01-17-2014, 04:21 PM

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

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

01-20-2014, 01:49 AM

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.

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

01-20-2014, 05:14 AM

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.

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.