Function that will find a specific word in a text and then updating value in another cell!

**AAL** · 04-24-2013, 03:37 PM

I'm working on a spread sheet and I need help adding onto a IF function to find a specific word in a text in another column to change a value whenever that word apears. Example below, please help!!!!

***The word I need the function to recognize is DMV to change the value in the adjacent cell to 0.00. while other cells in the same column of the 17.00 that do not have that key word to have their value remain unchanged. the cell for 17.00 has an IF formula in the cell already. Hope this helps!

CK-CHK*DMV-CITY*ERR CORR $17.00

**Tony Valko** · 04-24-2013, 03:45 PM

Not real clear.

If the cell contains DMV this formula will return 0:

=IF(ISERR(SEARCH("DMV",A2)),"",0)

**AAL** · 04-24-2013, 03:58 PM

Sorry If my question isn't real clear, what I need is a function or formula to find all cells in Column H that contain the word DMV in it, because I need all the values in Column I to be 0.00 on my sheet that correspond to to the adjacent cells that contain the word DMV and those cells in Column I that don't correspond with the word DMV to not change their value. hope this is more clear, thank you for your help!

Column H Column I
Row 2 CK-CHK*DMV-CITY*ERR CORR $17.00 <---need to change to 0.00
Row 3 abc corp *ctm*err corr $17.00 <----value stays the same because the adjecent cell doesn't have DMV in it

**Tony Valko** · 04-24-2013, 05:16 PM

Originally Posted by AAL

Column H Column I
Row 2 CK-CHK*DMV-CITY*ERR CORR $17.00 <---need to change to 0.00
Row 3 abc corp *ctm*err corr $17.00 <----value stays the same because the adjecent cell doesn't have DMV in it

If you actually want to change a cells value you'll need to use a VBA macro. I'm not much of a programmer so I can't help you with that.

On the other hand, you can use a formula (like the one I suggested) in another column.

**AAL** · 04-24-2013, 04:02 PM

=IF(G2=F2,17,((G2/F2)*17)) I would like to add the formula to the IF function I have to the left if you can help. Thank you!

**AAL** · 04-24-2013, 05:26 PM

Tony, Thank you for your help, the formula did work and It changed the value in the cell to 0.00 for all the cells that correspond with DMV, but the other values changed to #value is there a function that will not touch the other data and leave them to there original value (ex, if not function)?
CK-CHK*CASHIERING*PMT #VALUE!<---need function or formula to have this value not change example 17.00
CK-CHK*DMV-CITY*ERR CORR $0.00 <---correct

**Tony Valko** · 04-24-2013, 05:30 PM

Can you post a SMALL sample file that demonstrates what you want to do?

SMALL = about 20 rows and 5 columns (if you even need that many) worth of data

**AAL** · 04-24-2013, 05:50 PM

Please see attached sample it has your formula in column B, it changes the DMV key word text to 0.00, but I need the other data to remain the unchanged.

Thank you,

**Tony Valko** · 04-24-2013, 05:59 PM

If column B already contains an entry you replace that entry if you then enter a formula in the cell.

A cell can not contain both a manually entered value and a formula at the same time.

As I noted in my other reply, if column B already contains data and you want to change that data you can either do it manually or with an automated VBA macro.

The only other option is to use an additional column with a formula.

So, column B would contain the original entry and a formula in column C will generate either a new value or the original value depending on the conditions.

Is that something you would want to do?

**AAL** · 04-24-2013, 06:23 PM

Sorry about posting in another thread, I guess if creating another column would be easiest without going the macro route then I have no other choice, one last question with the formula you have me to find DMV can I also use the same formula in the same column to find another text in a different cell and have those values equal 0.00 as well? Thank you for all your help

**Tony Valko** · 04-24-2013, 09:28 PM

Let's get your original problem solved first.

Can you post another SMALL sample file that shows just your data? Don't include any formulas I've suggested. I just want to see your data as it appears before you do anything to it.

**AAL** · 04-25-2013, 08:56 AM

Ok the attached Excel file does nopt include any of the formulas you had given me. There is one formula that is in Column D, that needs to be in there. Again what I am trying to do is add on formulas to that will search for the word "DMV" AND "FTB" and zero out the amounts in column D while leaving the other amounts in column D their original value. Thank you!!!!!!

**Tony Valko** · 04-25-2013, 09:26 AM

OK, try this formula entered in E2 and copied down:

=IF(SUM(COUNTIF(C2,{"*DMV*","*FTB*"})),0,D2)

If the cell in column C contains either DMV or FTB the formula will return 0 otherwise, it'll return the value of the cell in column D.

**AAL** · 04-25-2013, 09:36 AM

Tony your da man!!! Thank you!!! Just a quick question, can I add this formula to the formula already in column D? Or do I have insert a new column? If I can add it onto the other formula how would I beable to add this to the formula in column D?

**Tony Valko** · 04-25-2013, 09:41 AM

To combine the formulas...

Entered in D2 and copied down:

=IF(SUM(COUNTIF(C2,{"*DMV*","*FTB*"})),0,IF(B2=A2,17,(B2/A2)*17))

**AAL** · 04-25-2013, 09:44 AM

Thank you so much for your time!!!! You have been really helpful and thank you for your patience!!!!

Function that will find a specific word in a text and then updating value in another cell!

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Function that will find a specific word in a text and then updating value in another cell!

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Re: Function that will find a specific word in a text and then updating value in another c

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions