If function contains a word

E

eeder1

Board Regular
Joined
May 15, 2008
Messages
104
Hello...I need to be able to identify the column below contains the word balance then say "yes" otherwise "no" I have done exact if functions but not one if part of a word is contained in the cell. Please help as I have thousands of lines unless I just want to chop this up but woudl prefer to do in one formula. Thanks for any help

<TABLE style="WIDTH: 157pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=209 border=0 x:str><COLGROUP><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 157pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" width=209 height=17>Acct</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Activity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Activity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Activity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Activity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Activity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" height=17>Allocations_Balance</TD></TR></TBODY></TABLE>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Thanks a million! How do you figure that kind of stuff out without asking someone? I googled for thirty minutes??
 
Upvote 0
You're welcome - thanks for coming back to say thanks.

To be honest, this is not really advanced stuff, you just pick it up. If you keep an interest in this board, you'll pick up alot of stuff like that.
 
Upvote 0
Ok, a follow up then...since i do not fully under the iserror formula you referenced what if I want it to = column A (old Acct) if it does not contain "Balance"?


IF(ISERROR(SEARCH("Balance",A2,1)),"No","Total_Inventory")

<TABLE style="WIDTH: 314pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=418 border=0 x:str><COLGROUP><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" span=2 width=209><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 157pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffc0" width=209 height=17>Old Acct</TD><TD class=xl72 id=td_post_1716813 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 157pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffc0" width=209>New Account</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Balance</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A2,1)),"No","Total_Inventory")'>Total_Inventory</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Balance</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A3,1)),"No","Total_Inventory")'>Total_Inventory</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Activity</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A4,1)),"No","Total_Inventory")'>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Balance</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A5,1)),"No","Total_Inventory")'>Total_Inventory</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Activity</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A6,1)),"No","Total_Inventory")'>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Activity</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A7,1)),"No","Total_Inventory")'>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffc0" height=20>Allocations_Activity</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISERROR(SEARCH("Balance",A8,1)),"No","Total_Inventory")'>No</TD></TR></TBODY></TABLE>
 
Upvote 0
"IF(ISERROR(SEARCH("Balance",A2,1)),"No","Total_Inventory")"

Just a small warning - iserror() checks for all errors, including syntactical ones in the formula's construction. with a search(), you're better of with isnumber(), cos:

=if(iserror(sm(a1:a10),"No sales - fire everyone",sum(a1:a10))

...is not the sort of error you want to mask. in general, always try to check for the condition at issue, not a general error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
452
smozgur
smozgur
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question
VBA code clean up
Replies
9
Views
389
mumps
mumps
E
  • Question
VBA for Web Scraping help
Replies
0
Views
858
epactheactor
E

Forum statistics

Threads
1,221,590
Messages
6,160,668
Members
451,662
Latest member
reelspike

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top