Excel: How to extract the text between the last set of square brackets in a cell

Question

I have a cell of text which has multiple timestamps in it surrounded by square brackets.

[ANon-02/05/2013 08:32:30 - 0:15 mins]

Update here

[JBloggs-07/05/2013 09:30:13 - 0:15 mins]

Update here

[JBloggs-09/05/2013 16:02:19 - 0:15 mins]

Update here

[ANon-10/05/2013 10:42:41 - 1:0 mins]

I would like to be able to extract just the last timestamp, i.e.

[ANon-10/05/2013 10:42:41 - 1:0 mins]

I can extract the first set of timestamps using the following, but I cannot work out how to edit the formula to give me the last timestamp.

=MID(I3,SEARCH("[",I3)+1,SEARCH("]",I3)-SEARCH("[",I3)+1)

I would by grateful if someone could assist.

you mean that after update #3 you have a concatenation of your 4 timestamps in one single cell? — MikeD, Jun 19 '13 at 09:28

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

as a modification to this post I'd propose to use

=MID(A1,FIND("|",SUBSTITUTE(A1,"[","|",LEN(A1) - LEN(SUBSTITUTE(A1,"[","")))),999)

or - if you also want to remove the enclosing brackets

=SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"[","|",LEN(A1) - LEN(SUBSTITUTE(A1,"[","")))),999),"[",""),"]","")

edited May 23 '17 at 11:56

Community

answered Jun 19 '13 at 09:50

MikeD

score 0 · Answer 2 · answered Jun 19 '13 at 10:28

0

based on the sample data you could also use

="["&TRIM(RIGHT(SUBSTITUTE(A1,"[",REPT(" ",500)),500))

answered Jun 19 '13 at 10:28

JosieP

2 Answers2