Forum Discussion

Adamreead's avatar
Adamreead
Copper Contributor
Dec 11, 2022
Solved

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

I'm trying to fill the cells with the right text but i'm facing this issue. can anybody help me with this, plesse?            
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Dec 11, 2022

    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.
Adamreead's avatar
Adamreead
Copper Contributor
Dec 11, 2022

HansVogelaar 

 

https://youtube.com/shorts/7f_HGd1Cv3Y?feature=share 

HansVogelaar's avatar
HansVogelaar
MVP
Dec 11, 2022

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.
  • Adamreead's avatar
    Adamreead
    Copper Contributor
    Dec 12, 2022

    HansVogelaar 

    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.

     

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 12, 2022

      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?

      • Adamreead's avatar
        Adamreead
        Copper Contributor
        Dec 12, 2022

        HansVogelaar 

        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=!AJuwAJXbRVYB9uE 

Resources