Getting text out of strings

[CyberLady]

Hi all,

I'm trying to pull out two types of text from following string of data (located within one cell).

The text I want to pull out is not always the same size, and the text I need to pull out is not always in the same spot...

Since the text string will vary, I will here show you only two example;

Example 1
Text string

;#{1}Categories (Sub & Main);#{8}General Corporate Documents;#{29}Shareholder meetings /AGM;#

Text 1 that I want to pull out (red); General Corporate Documents
Text 2 that I want to pull out (blue); Shareholder meetings /AGM
//the pink numbers will also vary

Example 2
Text string

;#{1}Categories (Sub & Main);#{10}ICT;#{40}Frame Agreement;#

Text 1 to pull out (red); ICT
Text 2 to pull out (blue); Frame Agreement
//the pink numbers will also vary

Hope that someone with a brigth head can help me with this

Thanks in advance for your help!

 

[Scott Huish]

They always seem to be in the same spot to me, after the 2nd set of brackets and the 3rd set of brackets. Is this correct?

 

[Burrgogi]

They always seem to be in the same spot to me, after the 2nd set of brackets and the 3rd set of brackets. Is this correct?

That is exactly what I had noticed as well. I'm still trying to master Excel functions so I've been trying to come up with a find & substitute combination. I'll have to sit back & learn on this one.

 

[CyberLady]

They always seem to be in the same spot to me, after the 2nd set of brackets and the 3rd set of brackets. Is this correct?

Yes, of course you are totally correct

 

[Scott Huish]

Excel Workbook
	A	B	C
1	;#{1}Categories (Sub & Main);#{8}General Corporate Documents;#{29}Shareholder meetings /AGM;#	General Corporate Documents	Shareholder meetings /AGM
2	;#{1}Categories (Sub & Main);#{10}ICT;#{40}Frame Agreement;#	ICT	Frame Agreement
Sheet1

 

[Burrgogi]

Well I am stumped. So far, I have the following where X in the formula represents the number of characters to extract after the Nth occurrence.

=MID(A1,FIND("^",SUBSTITUTE(A1,"}","^",2))*1,X)

I haven't been able to find a way to come up with X. Maybe I'm approaching this the wrong way?

Edit: Oops... for some reason I didn't see your post until just now Hot Pepper.

 

[Burrgogi]

I noticed in your solution that you have FIND("`"

How does the character ` come into play?

 

[Scott Huish]

It's replacing the second occurence of } with `, a character that is unlikely to appear in the string.

 

[Burrgogi]

It's replacing the second occurence of } with `, a character that is unlikely to appear in the string.

I see.

Would have been possible to produce the desired outcome using the formula that I posted above? Well maybe not that exact formula, but something similar?

 

[CyberLady]

Excel Workbook
	A	B	C
1	;#{1}Categories (Sub & Main);#{8}General Corporate Documents;#{29}Shareholder meetings /AGM;#	General Corporate Documents	Shareholder meetings /AGM
2	;#{1}Categories (Sub & Main);#{10}ICT;#{40}Frame Agreement;#	ICT	Frame Agreement
Sheet1

Hi, and thanks for your fast respond. The code you sent works brilliant But, I need a "default code" for B1 and C1 - in other words if I have a text string as A1 or A2 or another - the B1 and C1 code should be able to get the text behind bracket #2 and #3....

Thanks!