How to erase parts of a string of text

[gcefaloni]

Hi,

I have thousands of strings of text like this one in an excel sheet (Pembina Pipeline Corp. 3.54% 02-03-2025 Callable MTN) and what I would like to do is to build a formula that goes through those strings and remove all text to the right of the year in those strings. All strings have 3 important sets of information: The company name, the coupon rate (%) and the maturity of the bond (the date). Those 3 things are the only thing I would like to extract and the rest I would erase.

How would you go about removing the "Callable MTN" portion of that string so that the rightmost portion of every string is always the maturity date?

Thanks in advance for your help! It will be extremely appreciated!

 

[Peter_SSs]

=IFERROR(DATE(RIGHT(TRIM(MID(A2,FIND("%",A2)+2,10)),4),LEFT(TRIM(MID(A2,FIND("%",A2)+2,10)),2),MID(TRIM(MID(A2,FIND("%",A2)+2,10)),4,2)),"") was the answer.

The text string wasn't in the same format as my system date so it didn't recognize it as a date and therefore Value or Datevalue functions weren't working.

Hard to tell with dates like the sample whether 2 March or 3 Februaey but from your formula I'm guessing that the text is in m-d-y format and you want d-m-y. If so, you could also try this bit shorter one (add IFERROR if required).

Excel Workbook
	A	B	C	D
1	Text	Co	Rate	Maturity
2	Pembina Pipeline Corp. 3.54% 02-23-2025 Callable MTN	Pembina Pipeline Corp.	3.54%	23-02-2025
3	ABC Inc 10.00% 05-06-2025 Whatever	ABC Inc	10.00%	06-05-2025
Extract Info (3)