How to extract specific text in excel?

Question

How do I get a specific text only like "LHS" or "RHS"?

Thank you for helping.

You want to use LEFT or RIGHT and probably FIND. See this site — PeterT
– PeterT, Commented May 19, 2021 at 19:44
What do you mean extract it? If the cell contains RHS then say RHS else if it contains LHS then say LHS. Unless you're needing the index I don't see why you would complicate this. — Michael Z.
– Michael Z., Commented May 19, 2021 at 20:07
Or you could just REPLACE RHS or LHS with empty string if wanting to rid it from the cell — Michael Z.
– Michael Z., Commented May 19, 2021 at 20:08
Oops I meant SUBSTITUTE like this =SUBSTITUTE(A1, " RHS ", " ") — Michael Z.
– Michael Z., Commented May 19, 2021 at 20:13

P.b · Accepted Answer · 2021-05-20 11:50:57Z

1

Provided that there are no other 3 character combinations ending with HS you can use: =MID(A1,SEARCH("?HS",A1)+1,3)

If you want to be sure to only find LHS or RHS use: =MID(A1,IFERROR(SEARCH("LHS",A1),SEARCH("RHS",A1))+1,3)

(Change A1 in the formula to the cell you like to search in)

Edit: Left out spaces in front and after search value to find result at end of beginning of text

Edit2: Added the spaces again, implementing Chris Neilsen's workaround: =MID(" "&A1&" ",IFERROR(SEARCH(" LHS "," "&A1&" "),SEARCH(" RHS "," "&A1&" "))+1,3)

edited May 20, 2021 at 11:50

answered May 19, 2021 at 19:54

P.b

12k2 gold badges15 silver badges34 bronze badges

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

4 Comments

Kaiser Dragon Over a year ago

Thank you. I tried the formula and working perfect fine for middle sentence; however, there is an error if the RHS or LHS appears in front or end of the sentence. since cant find the values for it dues to no space in front or end of the string.

chris neilsen Over a year ago

@Kaiser ^^^ then replace A1 in the formula with " "&A1&" "

P.b Over a year ago

Ok, I removed the spaces, but then I recommend using the latter function.

P.b Over a year ago

And implemented Chris Neilsen's workaround in Edit2, because that's the nicest way.

bosco_yip · Accepted Answer · 2021-05-20 04:52:47Z

0

In B2, formula copied down :

=MID(A2,MIN(SEARCH({"lhs","rhs"},A2&"lhsrhs")),3)

answered May 20, 2021 at 4:52

bosco_yip

3,8322 gold badges8 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to extract specific text in excel?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related