时间戳

首页 > 解决方案 > How to extract middle characters from a cell in Excel

excel - How to extract middle characters from a cell in Excel

问题描述

I need a excel function code that would enable to me extract certain characters in the middle of a cell.

So in cell A74 is:

1625362674848-cdpresent-auths_ol_mart-auths1837372

So I Need to extract "auths_ol_mart" into a separate column

I have tried this:

=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)

Now the problem is this only gets "cdpresent". I am not quite sure how this is done.

more examples include:

3837463747-cdpresent-avaya_op_history-clm1827489

I want "avaya_op_history"

3734279458-cdpresent-uk_score_app-clm9377233

I want "uk_score_app"

Thank you all

标签： excelvbafunctionexcel-formula

解决方案

You can use this worksheet formula:

=LEFT(MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,99),FIND("-",MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,99))-1)

If you use the Formula Evaluation tool, you will be able to see how this works.

If you prefer a UDF, you can use:

Function betweenDashes(S As String) As String
    betweenDashes = Split(S, "-")(2)
End Function

Edit (20MAR2020)

Another formula to return the third item in the string, if you have Excel 2013+ and the FILTERXML function:

=FILTERXML("<t><s>" & SUBSTITUTE(A1,"-","</s><s>")& "</s></t>","//s[3]")

or, if you prefer, the next to last item:

=FILTERXML("<t><s>" & SUBSTITUTE(A1,"-","</s><s>")& "</s></t>","//s[last()-1]")

推荐阅读