How to get a specific text beside another text

[pinkfab]

Hi. I hope someone can help me.

1. I need to get the specific amount next to the word Principal

For example:

A1: bla bla bla xxx xxxx blabla bla bla bla blaPrincipal:1,000 xxx bla bla xxxx xxxx xxxxx bla bla bla 11/14/2017538.95Interest and bla bla bla

Basically, all of the data are pasted on A1. Like a hundreds of words pasted on 1 cell. These bunch of words arent consistent, but they all have this "principal" word in it.

But i only need the amount next to it (1,000)


2. Get the amount BEFORE the word "interest"
Same example, but I only need to get the amount 538.95.

Thanks a bunch.

 

[Peter_SSs]

1. Try
=REPLACE(LEFT(A1,FIND(" ",A1,SEARCH("Principal:",A1))),1,SEARCH("Principal:",A1)+9,"")+0

2. Is trickier as it appears in your example that further digits are immediately to the left of the required number. Is there always a date in that position? If so, is that date always formatted with a 2-digit day & a 4-digit year (eg 11/03/2017)?
Any further information &/or examples might help clarify.

 

[pinkfab]

Hello. Thanks for replying. Yes, it's always beside a date that is formatted exactly like that

 

[Peter_SSs]

Hello. Thanks for replying. Yes, it's always beside a date that is formatted exactly like that

OK, so for 2, try

=REPLACE(LEFT(A1,SEARCH("Interest",A1)-1),1,SEARCH("/??/",A1)+7,"")+0

 

[DanteAmor]

A couple of options:

1.
=--TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("Principal:",A1)+10,99)," ",REPT(" ",99)),50))

2.
=--TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("/??/",A1)+8,50),"Interest",REPT(" ",99)),50))

 

[Peter_SSs]

A couple of options:

1.
=--TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("Principal:",A1)+10,99)," ",REPT(" ",99)),50))

2.
=--TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("/??/",A1)+8,50),"Interest",REPT(" ",99)),50))

A couple of comments.

- In case it might be relevant for the OP, I note that both formulas are case-sensitive to "Principal" & "Interest".
Of course that issue could be eliminated from the formulas if required.

- TRIM() is redundant in both formulas as these would do the same job.

1.
=--LEFT(SUBSTITUTE(MID(A1,FIND("Principal:",A1)+10,99)," ",REPT(" ",99)),50)

2.
=--LEFT(SUBSTITUTE(MID(A1,SEARCH("/??/",A1)+8,50),"Interest",REPT(" ",99)),50)

 

[Rick Rothstein]

Here another formula you can try for your first formula...

=LOOKUP(9.9E+307,--LEFT(MID(A1,SEARCH("principal",A1)+10,99),ROW($1:$99)))

 

[Peter_SSs]

Here another formula you can try for your first formula...

=LOOKUP(9.9E+307,--LEFT(MID(A1,SEARCH("principal",A1)+10,99),ROW($1:$99)))

A couple more comments

- This is can give incorrect results if rows are subsequently added above the formula (eg with the OP's example, 6 or more rows)

- This could possibly give incorrect results if certain other text/number combinations exist. For example, it produces 43534 for the text below.

"bla bla blaPrincipal:10 Markets to be checked on 11/14/2017538.95Interest and bla bla bla "

 

[DanteAmor]

A couple of comments.

- In case it might be relevant for the OP, I note that both formulas are case-sensitive to "Principal" & "Interest".
Of course that issue could be eliminated from the formulas if required.

- TRIM() is redundant in both formulas as these would do the same job.

Thanks Peter for the comments. I put the updated formulas without TRIM() and to work with case-sensitive. In fact, now formula 1 is more compact.

1.
=--LEFT(SUBSTITUTE(MID(A1,SEARCH("principal:",A1)+10,50)," ",REPT(" ",99)),50)

2.
=--LEFT(SUBSTITUTE(MID(LOWER(A1),SEARCH("/??/",A1)+8,50),"interest",REPT(" ",99)),50)