10

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.

5 Answers 5

12

Use SUBSTITUTE.

=SUBSTITUTE(A2,"Application: ","")
3

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.

1

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:"

-1
=TRIM(RIGHT(A2,LEN(A2)-FIND(":",A2,1)))
2
-3

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)

You must log in to answer this question.

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