Remove a pattern of text from cells

[shane1800]

Hi,

Looking for some help on cleaning up data...

I have a large amount of data that looks like this, here are 4 rows.

I'd like to automatically remove all the "Borrower added on XX/XX/XX > " text and "XXXXXX added on XX/XX/XX > " text.

The dates and special ID number change depending on the cell.

------------


Borrower added on 01/19/10 > I plan to refinance a loan I had previously taken out several years ago. This loan still has 40 months left with a monthly payment of $537. I plan to reduce the monthly payment so I can put more money on a car loan to pay off quicker.<br/>

Borrower added on 03/15/11 > Using to payoff high interest rate credit card<br/> Borrower added on 03/16/11 > I have no late payments listed on my credit report (ever).<br/>Using this loan will reduce my financial burden and increase my spare funds.<br/>My job is a fully funded government contract.<br/>

589166 added on 12/11/09 > Trying to get my debt under control so I can give my daughters what they deserve.<br/>

588059 added on 12/09/09 > I have two other sources of income which amout to $1500 per month.<br/> 588059 added on 12/10/09 > I have been with my current employer for 23 years. There is an error on the details page.<br/>

[Moo the Dog]

Assuming your data starts in A1 (change reference to suit your needs), put this in the cell next to it:

=SUBSTITUTE(MID(A1,FIND(">",A1)+2,LEN(A1)),"<br/>","")

This will get rid of the "Borrower added..." and "xxxxx added..." parts, as well as the <br/> html on the end, leaving you with only the text desired.

- Moo

P.S. - If you don't need to remove the "<br/>" then just use:

=MID(A1,FIND(">",A1)+2,LEN(A1))

[shane1800]

Thanks for the guidance!

It's looking good and am glad to learn more functions.

I didn't fully mention that the text to delete could be mentioned in the beginning, middle, end, and several times in a cell. I attached an example spreadsheet. Current formula cuts off too much text on some cells...

[shane1800]

I will ask commercial services and report back.

[shane1800]

Solved here

Please Login or Register  to view this content.