PDA

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>

GTO
01-14-2014, 12:23 PM
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.

snb
01-17-2014, 09:31 AM
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
01-17-2014, 11:30 AM
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

snb
01-19-2014, 09:58 AM
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.

Bob Phillips
01-20-2014, 05:07 AM
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.

Bob Phillips
01-20-2014, 05:23 AM
Yeah, there is always something you can do, but it still keeps happening.