IF Statement - Getting inputted data to output a certain word in a different cell

[WhiteZorox]

Hi, so basically I've got a spreadsheet which people can input data into a cell, and I want a function that will search that cell for a keyword and output something into a separate cell if that keyword is present

What I've got so far:

=IF(SEARCH("Email", F29),A2 "Email Present", "No Email Present"))

At the moment it just returns with:


There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

• you type: =1+1, cell shows: 2

To get around this, type an apostrophe ( ' ) first:

• you type: '=1+1, cell shows: =1+1

[Sam Capricci]

you have too many arguments, your if search is saying if you fine email in f29, return A2, but it doesn't know what to do with the "email present" part.
maybe you need this ... =IF(SEARCH("Email", F29),A2&"Email Present", "No Email Present")

edit: in other words, what does A2 have to do with the formula? if you are trying to join it with "email present" then what I gave you will do that, if not, then maybe remove A2 from the formula?

[jason.b75]

Hi, welcome to excel forum!

Firstly, there is a finicky rule that says I should advise you that your thread title is not good enough, another finicky rule means that because of the first rule, that we are not allowed to help you until you have changed your thread title to something more acceptable.

As it's your first post on your first day on the forum, I think that quoting the rule book is a bit harsh, but please take this as a gentle nudge to take a quick read of them and maybe make your title a bit more descriptive

As I haven't actually advised you that your thread title is not good enough, but merely advised you that I should advise you, I can still provide you with some assistance.

Try something like

=IF(ISNUMBER(SEARCH("email",F29)),"Email Present","No Email Present")

2 things to note, firstly the search string "email" will specifically look for that text, not something that looks like an email address.
Secondly, you cannot use a formula to 'send' something to another cell, as it appears that might be what you are trying to do. The formula can only show an answer in the cell which you enter it.

Does this help with what you are tryng to do?

[WhiteZorox]

Hi Jason,

Yeah sorry about that I didn't realise also sorry for the extremely late reply, I'm doing this spreadsheet for my work and I had completely forgotten about this project.

The formula you had given isn't working I pasted it into excel and I got the following error:

'There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly.'

Yeah I understand it won't find email addresses I want it to find key words and as a result of that keyword I want it to output another word.
My only programming experience is with Visual Basic, Small Basic, Python and C#.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[WhiteZorox]

Sure, I've literally just left the office so tomorrow I'll attach the workbook I will remove the confidential information don't want to have ICOs on my back lol

[WhiteZorox]

I can't tell if the documents are attached but when I go on 'Manage Attachments' they seems to be attached, I don't know.

I'm attempted to allow people to just copy and paste information from our communications logging system onto the invoice spreadsheet so it will automatically input the data rather than spending hours inputting all the data ourselves.

The code's I've discovered so far are:

This I had tried out myself and I couldn't get it to work.

=IF(OR(ISNUMBER(SEARCH("email",C14)),ISNUMBER(SEARCH("Telephone",C14)),MORE CRITERIA GO HERE, MORE CRITERIA GO HERE),"Case Manager Email","THISPARTWILLBEONEOFTHEOTHERCRITERIA")

This is the one Jason had given me. (Except I had just changed some variables for my situation)

=IF(ISNUMBER(SEARCH("email",C14)),"Case Manager Email","THISPARTWILLBEONEOFTHEOTHERCRITERIA")

[Glenn Kennedy]

Two files and a total of four sheets... and no indication of where the formula is needed and some mocked-up results.

Confused...

[WhiteZorox]

The formula is needed from C14 down to C64 and the file 'Goal.xlsx' is a sample of what is looks like when completed, there are also some hidden sheets if you want to have a look at them for whatever reason.

[jason.b75]

I don't get what you're trying to achieve.

I'm assuming C14:C64 in the Activity sheet but as you mentioned copy and paste from another source, so it would be more logical that the Activity sheet would be where you paste this data, so it can't have formulas where you're pasting. Also C14:C64 already conatain validation dropdowns.

Based on the Goal sample, please explain what you want the formula in C14 to do. Where should it take information from, and what should it do with that information?

From what I've read so far, it is my guess that you're confusing the purpose of a formula with that of a vba procedure.