Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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
up vote 8 down vote accepted


=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 don't you just use =REPLACE(A2,1,12,"") this replaces characters 1-12 with "" (nothing) and so just displays the lookup (A2) from the 13th character, removing "Application:"

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .