Using substitution with a changing value

[neelesh2003]

Hi,

In cell a1 I have the following text "45 students fail test 5 of report" ok so I want to remove test 5 from the cell so I used =subsitiution(a1,"test 5","") and this worked but my cell a1 the number of test keep changing from test 1 to test 2 all the way up top test 9, so all I want to do is remove test #, so I tried the following formula and it didn't work.

=subsitution(a1,"test #","") this not not working and it is driving me bonkers, how do I write this formula so I can remove the test and number after test,


Ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!

 

[Richard Schollar]

Hi

Given it only goes 1 thru 9 you can probably use:

Excel Workbook
	A	B
1	45 students fail test 5 of report	45 students fail of report
Sheet1

Excel 2002

Cell Formulas
Range	Formula
B1	=REPLACE(A1,SEARCH("test ",A1&"test "),6,"")

 

[neelesh2003]

What is the 6 in your formula for.

 

[Richard Schollar]

6 characters replaced by an empty string ie "test #"