Search Specific text

[Lasantha]

Dear All,


Could you please help me to search a word in a cell which starting with “Ref:” . please see the below example. This is the cell value. So I need to get the date next to “Ref:”


Could you please create formula for this.


“Check Payment from Guarantor (5128) on 07/02/2018 Ref: 07/02/2018 07022018 Lock box $1583.54 BOA PG#54”

 

[bosco_yip]

Try.................

In C2, copied down (for UK Date setting user only) :

=0+MID(REPLACE(A2,FIND("Ref:",A2)+11,,MID(A2,FIND("Ref:",A2)+5,3)),FIND("Ref:",A2)+8,10)

p.s.

1] The date after the wording "Ref:" appeared in USA date type.

2] So, if you are using USA Date setting, please use Vletm's formula (in post #.3).

3] If you are using UK Date setting, please used the above longer formula.



Regards
Bosco

 

[vletm]

Lasantha
=mid(your_txt, find("Ref: ",your_txt,1)+5,10)

 

[Lasantha]

Try.................

In C2, copied down (for UK Date setting user only) :

=0+MID(REPLACE(A2,FIND("Ref:",A2)+11,,MID(A2,FIND("Ref:",A2)+5,3)),FIND("Ref:",A2)+8,10)

p.s.

1] The date after the wording "Ref:" appeared in USA date type.

2] So, if you are using USA Date setting, please use Vletm's formula (in post #.3).

3] If you are using UK Date setting, please used the above longer formula.



Regards
Bosco

Thank you so much...

 

[Lasantha]

Lasantha
=mid(your_txt, find("Ref: ",your_txt,1)+5,10)

Thank you so much...