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

3 Answers 3

up vote 5 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

Why on earth do you think =RIGHT(A2,FIND(" ",A2)+4) is a reasonable way to call RIGHT()?  Where does the 4 come from?  What does it signify?

The usage of RIGHT is

RIGHT(text, [num_chars])

i.e., the second parameter is the number of characters that you want to extract.

Meanwhile, FIND(" ", A2) is the position of the first space in A2, which is always 13 as long as the first 12 characters are Application: and the 13th is a space.  So FIND(" ",A2)+4 is always 17; you are always getting the 17 rightmost characters.

To get everything after the Application: , use

=RIGHT(A2, LEN(A2)-FIND(" ", A2))

or simply

=RIGHT(A2, LEN(A2)-13)
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.