SOLVED

Excel fills unselected cells, and the formula is duplicating without a reason

Occasional Contributor

I'm trying to fill the cells with the right text but i'm facing this issue. can anybody help me with this, plesse?

 

 

Untitled.png

Untitledfg4.png

8 Replies

@Adamreead 

Since C3:C13 is a range of 13 cells, SEARCH("...",C13) also returns a range of 13 cells in Excel in Microsoft 365 and Office 2021.

What exactly do you want to accomplish?

@Hans Vogelaar if Excel found the "a word" within C3:C13 write the word O.Two.O in the selected cells in T column. 

best response confirmed by Adamreead (Occasional Contributor)
Solution

@Adamreead 

You have two options.

 

Option 1:

  • Select cell T3.
  • Enter the formula =IF(ISNUMBER(SEARCH(C3,"...")),"O.Two.O","")
  • Replace ... with the Arabic (?) text.
  • Fill down.

Or:

Option 2:

  • Select T3:T13612, as in the video.
  • Enter the formula =IF(ISNUMBER(SEARCH(C3:C13612,"...")),"O.Two.O","")
  • Replace ... with the Arabic (?) text.
  • Confirm the formula by pressing Tab or Enter, not by pressing Ctrl+Enter.

@Hans Vogelaar 

Thank you so much for helping me I tried my first formula =IF(SEARCH("...",C3),"O.Two.O")
but only selected C3 cell not (C3:C13612) and the spill error was gone.

My Excel is acting very strange because C3 doesn't even have the word in Arabic that is in the formula but Excel still managed to find the corresponding cells in T column and filled them down with O.Two.O.
I also I tried the formula =IF(SEARCH("او تو او",C3),"O.Two.O", "") but I got the Value error but it's easy to get rid of not like Spill errors.

When I tried the formulas you provided me they didn't work for some reason.

 

Untdgdfitled.png

@Adamreead 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 

I managed to solves the problem for the formulas you provided me

 

I just swaped "washing" and A1:A6 places as in the example below and it did the job.

 

=IF(ISNUMBER(SEARCH("wishing", A1:A6)),"wishing", "")

 

=IF(ISNUMBER(SEARCH("wishing", A1)),"wishing", "")

 

and these two formulas are also working but I can't remove the value errros for the empty cells.

=IF(SEARCH("wishing",A1),"wishing", "")

 

=IF(SEARCH("wishing",A1:A6),"wishing", "")

 

thank you so much for your time I appreciate it so much.

 

https://onedrive.live.com/edit.aspx?resid=A957B62CA0D07013!111&ithint=file%2cxlsx&authkey=!AJuwAJXbR... 

@Adamreead 

Instead of SEARCH("wishing", ...) you have to use ISNUMBER(SEARCH("wishing", ...))