CODEWITHSUNDEEP
excelexcel-formula
Srpic
Srpic

Reputation: 450

Excel - split cell value by specific string?

I have cells that contain a lot of text, but all of them contain string "RTG Actions: ". I would like to remove the text before the string ("RTG Actions: ") and keep only the text after it.

I have a formula that almost does the job:

=RIGHT(P6,LEN(P6)-FIND("RTG",P6))

Unfortunately it keeps also a part of the string with it. i.e. "TG Actions: eams will leverage the Command and Control framework to..."

Could you advise me, how can I remove "TG Actions: ", please?

Many thanks!

Upvotes: 0

Views: 428

Answers (2)

Kevin
Kevin

Reputation: 2631

You can use RIGHT or MID function (I prefer MID here):

=RIGHT(P6,LEN(P6)-FIND("RTG Actions:",P6)-LEN("RTG Actions:"))

=MID(P6, FIND("RTG Actions:",P6)+LEN("RTG Actions:"), LEN(P6))

For the MID FIND gets the beginning index of "RTG Actions:" and adds the length of it to get the starting point LEN(P6) ensures you get all the remaining characters.

Upvotes: 1

urdearboy
urdearboy

Reputation: 14580

Here is another option using the SEARCH function. It is essentially the same as the above, but just using a different function.

Since your string to search for is constant, I just swapped LEN("RTG Actions: ") with it's character count (13 if you include a lagging space)

=MID(P6,SEARCH("RTG Actions:",P6)+13,LEN(P6))

Upvotes: 0

Related Questions