Replace across columns

I am trying to use the Find/Replace function in Excel 2010 to find a particular cell and replace it and the (say) 3 cells to it's right with blanks.

Something like replacing all the S123 's in :-

S012 a 50 34
S098 b 46 21
S123 v 27 19
S564 cc 11 67
S123 v 27 19
S577 f 45 21

to become
S012 a 50 34
S098 b 46 21

S564 cc 11 67

S577 f 45 21

I'd prefer NOT to use VBA coding.
Any suggestions please
Ken

Thanks..
I was hoping that there could be something like {"","","",""} which is the syntax used when one defines a Name across several blank columns

You can use Find & Replace (CTRL-H) to replace all the S123's in column A, if you highlight the column first and do it in one operation. You would then have to manually move down to the next empty cell in column A (END key followed by Down-Arrow), then select the 3 cells to the right (i.e. v 27 19) then press the delete key, and then repeat this for all the other blanks in column A.

Hope this helps.

Pete

Thanks Pete,
I'm actually doing similar to that. The problem is that there are multiple ( had 36 in one workbook) that require deletion and nulls in the cells to the right.
Repeating your sequence is rather time consuming.

That's why AliGW said in Post #2 that you would need VBA - you won't be able to do it with a formula.

Well, you could, in fact, use 4 formulae in different columns. For example, if your data occupies columns A to D, then you could use these formulae in the cells stated:

F1: =IF(A1="S123","",A1)

G1: = IF($F1="","",B1)

then copy G1 into H1 and I1, then copy those formulae down as far as you need them.

You could then fix the values, and then delete columns A to E, and that manual operation will not take up a lot of time.

Hope this helps.

Pete

Thanks again Pete. I had considered something similar but the problem is the item to delete changes AND there are thousands of rows spread over 8 worksheets (Monday,Tuesday, etc ) . The find and replace option seems the simplest. I am currently using Find, then pasting 4 blank cells over each occurrence . A bit tedious when done 36 times. A macro would help but would still need to execute the macro multiple (perhaps 36 ) times.