CODEWITHSUNDEEP
excelfunction
FiddleDeDee
FiddleDeDee

Reputation: 183

Conditional data entry on certain text in excel

I'm after some pointers.

I want to input text into some cells based on the contents of others.

Current formula that I'm using in cell B1 is

=IF(ISERROR(FIND("example",A1)),"no","yes")

So far, so simple. This works. Inputs 'yes' in B1 if 'example' is located in the cell A1, copy down. However, there are some cells in the B column which have already been formatted with other text in, and I want to apply this formula on the entire spreadsheet without removing the existing text in column B. So far, the only thing I can think of is by putting this formula in the C column, copying down then moving the text from the C column to the B column - but this requires laborious copy and pasting that kind of negates the time saving provided by the formula.

What is the best way to achieve this? Do I need to resort to VB to scan the entire sheet, iterating through A1-A30000 and inserting the requisite text in B column?

Cheers.

Edit:

So, this has been pretty much sorted, with a variety of suggestions.

However, the actual formula I'm using is:

=IF(ISERROR(FIND("example",A1)),"","yes")

as I only want text to be put in columnB if the pattern is found. This has presented another problem! The double quotes signifying no replacement if the pattern ISN'T found actually inserts a 'NULL' character in there; when you try and select the blank cells on the next iteration of the operation, no cells are selected, as even though there is no visible character, Excel thinks there is. xD

This is solved by writing a macro function to remove Null characters.

Excel is infuriating sometimes. :P

Upvotes: 1

Views: 2723

Answers (3)

user3616725
user3616725

Reputation: 3655

All of the information and advice already given is definitely correct and gets the job done, byt thought I'd add one that does not require ANY changes to the existing data (eg sorting or filtering):

Put your formula as you described is cell B1. Apply any conditional formatting to that cell, as required.

Now select and copy (ctrl+C) cell B1. Select column B (click on column heading). Then Click find and Select (binoculars icon) - on Home ribbon tab. Choose go to Special.

In the popup choose Blanks and click ok. You will note all blank cells in colum B are selected. Don’t click anything, press Crtl-V.

Voilà, your formula and conditional formatting is pasted into all blank cells, leaving the rest alone.

enter image description here

Quick keyboard sequence:

  • select B1
  • Press ‘Ctrl + C’
  • Ctrl + ‘Space Bar’
  • Press ‘Ctrl + G’
  • Press Tab twice to go to ‘Special…’ button, press Enter
  • Press ‘K’ for blanks, Press Enter
  • Press ‘Ctrl + V’
  • (optional) press escape to remove selection from B1

Upvotes: 2

pnuts
pnuts

Reputation: 59440

You might combine your existing B column values with those to be added conditionally in a single formula, say in ColumnC, such as: 

=IF(ISBLANK(B1),IF(ISERROR(FIND("example",A1)),"no","yes"),B1)

copied down to suit. Then if ColumnB has become redundant, copy ColumnC, Paste Special, Values over the top and delete ColumnB. The result is to convert the formulae to the results thereof, that may increase processing speed but reduce the ease of handling additional rows (if any) subject to the same requirements.

Upvotes: 1

Bernard Saucier
Bernard Saucier

Reputation: 2270

There are a few ways to deal with this. I'll explain 2 of them :

  1. If sorting is an option, sort your table by column B (ascending / A-Z) to get all the empty values at the top. Write your formula in the first blank cell and extend it by double-clicking the bottom-right corner of that cell.

  2. If you cannot sort, apply filters to the table and select only the blank cells in column B. Then write the formula in the first blank cell and extend it by dragging it down to the last cell.

Upvotes: 1

Related Questions