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!

 

[Rick Rothstein]

Assuming your single example is truly representative, give this formula a try...

=LEFT(A1,FIND("%",A1)+11)

 

[gcefaloni]

Assuming your single example is truly representative, give this formula a try...

=LEFT(A1,FIND("%",A1)+11)

Yea I thought of doing something like that.. Going from the % sign and basically take the numbers between that % and the space to the left of that % to get the coupon rate and then take 11 characters to the right of that % sign to get the maturity but I can't figure out how to do it. The formula you sent me seems interesting, I think it works to erase the right most info.

 

[Rick Rothstein]

The formula you sent me seems interesting but it returns the entire string of text without the rightmost 11 characters when I try it.

I do not see how that is possible with the formula I gave you. Did you use the exact formula I posted or did you modify it in some way? In my tests, the formula I posted returns exactly what you asked for (everything up to and including the year).

...to get the coupon rate and then take 11 characters to the right of that % sign to get the maturity...

Are you looking for something other than what you asked for originally? Your first post said...

"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?"

 

[gcefaloni]

Assuming your single example is truly representative, give this formula a try...

=LEFT(A1,FIND("%",A1)+11)

If I'm using: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)-1)," ",REPT(" ",300)),300)) to take the coupon rate between the % and the space before the %. What should I do to capture the maturity date to the right of that same % symbol?

P.S. I edited my initial reply. Your formula worked like a charm to remove the rightmost portion.

P.S.2: I'm not sure I understand that formula fully but I think =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)+12)," ",REPT(" ",300)),300)) works for the right portion of the %.

 

[gcefaloni]

Seems to be working. Thanks a ton Rick.

 

[Peter_SSs]

I see that you have things working, but I'm wondering if any of these are of interest. In this case the Date and Rate values are returned as numbers (formatted to show Date & Percentage) as I thought the results may end up being used numerically elsewhere? For example, cell C2 actually contains 0.0354 showing 3.54% when formatted as percentage.

I have assumed that the coupon rate will never exceed 99.99% and will always be 2 decimal places in the text format.

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

If you just want the coupon rate as text
=TRIM(MID(A2,FIND("%",A2)-5,6))

If you just want the date as text, leave off the +0 from the end of that formula.

 

[gcefaloni]

I see that you have things working, but I'm wondering if any of these are of interest. In this case the Date and Rate values are returned as numbers (formatted to show Date & Percentage) as I thought the results may end up being used numerically elsewhere? For example, cell C2 actually contains 0.0354 showing 3.54% when formatted as percentage.

I have assumed that the coupon rate will never exceed 99.99% and will always be 2 decimal places in the text format.

Extract Info

	A	B	C	D
Text	Co
Pembina Pipeline Corp. 3.54% 02-03-2025 Callable MTN	Pembina Pipeline Corp.
ABC Inc 10.00% 05-06-2025 Whatever	ABC Inc

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:369px;"><col style="width:158px;"><col style="width:72px;"><col style="width:98px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]Rate[/TD]
[TD="align: right"]Maturity[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]3.54%[/TD]
[TD="align: right"]02-03-2025[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]10.00%[/TD]
[TD="align: right"]05-06-2025[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =TRIM(LEFT(A2,FIND("%",A2)-6)) C2 =MID(A2,FIND("%",A2)-5,6)+0 D2 =MID(A2,FIND("%",A2)+2,11)+0 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


If you just want the coupon rate as text
=TRIM(MID(A2,FIND("%",A2)-5,6))

If you just want the date as text, leave off the +0 from the end of that formula.

That's also extremely useful! Thanks a bunch!

 

[gcefaloni]

Actually, the maturity date doesn't work with +0 for me on some text and it works on others.
Not sure why.. I'm investigating.

 

[gcefaloni]

Actually, the maturity date doesn't work with +0 for me on some text and it works on others.
Not sure why.. I'm investigating.

=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.