extract part of text

[tushararora]

Hi I need to extract a part of text. Example:

From: Deborah A Ellis [mailto:[email protected]] Sent: Tuesday, July 11, 2017 7:49 PM Subject: AURELIUS - Sale of Getronics Group completed - Biggest exit in history The previously announced EUR 220 million

Herein I need to extract - "AURELIUS - Sale of Getronics Group completed - Biggest exit in history" this is part between subject: and double space before "The previously announced EUR 220 million"

how can i do this ?

[Roel Jongman]

with the whole text in A1 try

=MID(A1,SEARCH("subject:",A1)+9,SEARCH("  ",A1)-9-SEARCH("subject:",A1))  

[Pete_UK]

There isn't a double space in that string when I copy it from the forum (the forum software removes double spaces), but assuming it is there and the string is in A1, you can use this:

=MID(A1,SEARCH("subject:",A1)+9,SEARCH(" ",A1)-SEARCH("subject:",A1)-9)

Hope this helps.

Pete

[shukla.ankur281190]

Excel 2016 (Windows) 64 bit

	A
1	From: Deborah A Ellis [mailto:[email protected]] Sent: Tuesday, July 11, 2017 7:49 PM Subject: AURELIUS - Sale of Getronics Group completed - Biggest exit in history The previously announced EUR 220 million
2	AURELIUS - Sale of Getronics Group completed - Biggest exit in history The previously announced EUR 220 million

Sheet: Sheet1

A2

Please Login or Register  to view this content.

[tushararora]

Thanks Roel , Pete and Ankur.

@Ankur - I just need the text till history. After history there are 2 spaces.

[tushararora]

@Roel and Pete - thanks for helping but some how that formula inst working! its giving VALUE! error.

[shukla.ankur281190]

Then try


=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ","@"),"Subject: ",REPT(" ",LEN(A1))),LEN(A1))),"@",REPT(" ",LEN(A1))),LEN(A1)))

[Pete_UK]

@Roel and Pete - thanks for helping but some how that formula inst working! its giving VALUE! error.

Check that you do actually have a double-space in the text. It may include a non-breaking space character (code 160).

Hope this helps.

Pete