Formula to retrieve characters from a cell

[Abgar]

Hi guys,

Looking for a formula that will find the last occurrence of a character in a cell, and then show that character, plus 3 characters before and after.

In the example below, i am looking for the occurance of a colon ( : ) in A1 and showing the 3 characters before and after, to return the contents of cell B1. (i.e. - the formula goes in B1 and returns the value)

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:192px;"><col style="width:58px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1
</td><td>01a887nno123:987thnpx120</td><td>123:987</td></tr></tbody></table>
There "shouldn't" be any cells that contain 2 colons in the same text, but just on the chance that there are, it will always be the last occurance (the righthand most one) that i would need to return.

Can this be done in a simple formula?

Thanks so much for your help as always

 

[sanrv1f]

Try this

Excel Workbook
	A	B
1	01a887nno123:987thnpx120	123:987
2	01a887nno123:987thnpx:120	npx:120
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=MID(A1,SEARCH(CHAR(10),SUBSTITUTE(A1,":",CHAR(10),LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))-3,7)

 

[VoG]

With only a single colon try

=MID(A1,FIND(":",A1)-3,7)

 

[Roger Govier]

Hi

=MID(A1,FIND(":",A1)-3,7)

 

[Abgar]

Great guys - thanks so much for your help