Excel formula to extract string from text [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Closed 6 years ago.

I'm using the following formula to extract a string from text in a cell by getting rid of -DCS.

The text contained in the cell: Q074-SARE_MANSONG-DCS

Formula:IF(ISNUMBER(SEARCH("DCS",H22)),LEFT(H22,FIND("@",SUBSTITUTE(H22,"-DCS","@",LEN(H22)-LEN(SUBSTITUTE(H22,"-",""))))-1),([@[<HELPER><SITE>]]))

Desired reslut: Q074-SARE_MANSONG

The problem is that it is not working for all cells. Can someone please tell me what it is that I'm doing wrong. Thanks.

next time make sure to post some sample data especially the ones that are not returning the expected result. — Terry W
– Terry W, Commented Aug 29, 2019 at 0:02

JvdV · Accepted Answer · 2019-08-28 16:01:01Z

3

If "-DCS" is always at the end of the string:

=SUBSTITUTE(A1,"-DCS","")

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

answered Aug 28, 2019 at 15:45

cybernetic.nomad

6,5763 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark S. · Accepted Answer · 2019-08-28 16:12:00Z

3

Use one of these formulas instead (assuming your data is in A1)

=left(A1,len(A1)-4) or =SUBSTITUTE(A1,"-DCS","")

answered Aug 28, 2019 at 15:44

Mark S.

2,8911 gold badge10 silver badges31 bronze badges