Extract words of a certain portion

[RAMU]

Dear Friends,

I have few words like the following:

* ABC LIMITED * ~~TIN : 58976532147
* XYZ (P) LIMITED * ~~TIN : 25648793251

I want to extract only ABC LIMITED & XYZ (P) LIMITED

Pls provide me a formula.

Regards
RAMU

 

[VBA Geek]

are they always between 2 asterisks? if not you could provide more samples

 

[RAMU]

Yes sir, those are always between 2 asterisks & just after the asterisks ~~ sign is available.

 

[VBA Geek]

=mid(a1,1+search("~*",a1),search("~*",a1,search("~*",a1)+1)-search("~*",a1)-1)

 

[Haluk]

Alternative:

=TRIM(MID(SUBSTITUTE(A1,"*",REPT(" ",100)),100,100))

 

[RAMU]

wOOWW...!!! It works gr8.

 

[RAMU]

Dear Sir,

If * are absent, & only ~ is available, then how can I extract ?

Pls provide that formula.

=mid(a1,1+search("~*",a1),search("~*",a1,search("~*",a1)+1)-search("~*",a1)-1)

 

[jtakw]

Hi,

This will work whether there's an * at the beginning, before the ~ or Not:

Excel 2010
	A	B	C
1	* ABC LIMITED * ~~TIN : 58976532147		ABC LIMITED
2	* XYZ (P) LIMITED * ~~TIN : 25648793251		XYZ (P) LIMITED
3	* ABC LIMITED ~~TIN : 58976532147		ABC LIMITED
4	XYZ (P) LIMITED ~~TIN : 25648793251		XYZ (P) LIMITED
Sheet13

Cell Formulas
Range	Formula
C1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"*",REPT(" ",100)),"~",REPT(" ",100)),IF(CODE(A1)=42,100,1),100))

 

[markmzz]

Dear Sir,

If * are absent, & only ~ is available, then how can I extract ?

Pls provide that formula.

Try this too:

=TRIM(SUBSTITUTE(LEFT(A1,FIND("~",A1)-1),"*",""))

Markmzz