Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using find to remove some of the test string in lookup cell value

For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change it to sales_rep_support.

However I run into problems because I have other values that need parsed such as

Application: EComm_sk
Application: PG_Prescreen
Application: sales_rep_support
Application: Retention_sk

etc....

What I have been trying to accomplish is simply remove the Application: from the beginning of the string

I have also tried left() unsucessfully.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Use SUBSTITUTE.

=SUBSTITUTE(A2,"Application: ","")
share|improve this answer

Actually, I think that the simplest way is to use the Find & Replace on the column concerned if you intend to delete the original later on.

  1. Hit Ctrl+H.

  2. Find Application: ("Application", colon, space) Replace by nothing

  3. In the Find & Replace window, click on Options >> and make sure that the 'Within: ' is set to "Sheet" and that 'Match entire cell contents' is unchecked.


Otherwise, another function besides SUBSTITUTE() you can use is MID():

=MID(text, start, length)

In your case, you can use:

=MID(A2, 14, LEN(A2))

Which will take everything from the character position 14 (After all the characters in "Application: ") to the end of the text. LEN(A2) is actually larger than the text you want, but that doesn't matter, it means it'll take everything till the end.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.