[Solved] Check for specific text within text in a cell

Discuss the spreadsheet application

[Solved] Check for specific text within text in a cell

Postby cid » Sun May 12, 2019 12:39 am

Right now I have a formula that adds together a range of cells corresponding to another column of cells, but only the cells that contain specific text ("TEXT" in the example):

Code: Select all   Expand viewCollapse view
=SUMPRODUCT(G8:G58="TEXT"; J8:J58)


However, sometimes the text might be entered in the G-Range as "TEXT abc", where "abc" is any possible text afterwards. It can be put into any box in the column with anything written after "TEXT". All I can control is that the text that's being searched for is spelled the same way every time and always comes in front of the variable text.

So how do I get my code to trigger no matter what else is written in the cell besides "TEXT"? Is there a way to search for if the string "TEXT" is present in any position and non-case sensitive?

Also, is the "SUMIF" command better in this case or is it essentially the same?

Also if you're wondering I do have other code checking for if it's anything other than "TEXT" but it's being used in a different formula.

Thanks!
Last edited by cid on Sun May 12, 2019 2:39 am, edited 1 time in total.
OpenOffice 4.1.5 on Window 10
cid
 
Posts: 4
Joined: Sat May 11, 2019 12:42 pm

Re: How to check for specific text within text in a cell

Postby FJCC » Sun May 12, 2019 2:12 am

Try
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(ISNUMBER(SEARCH("^TEXT";G8:G58));J8:J58)

The ^ at the start of the search string assures that TEXT is at the beginning of the text. For this to work, you have to go to the menu Tools -> Options, expand the Calc list on the left, select Calculate and turn non Enable Regular Expressions in Formulas.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7263
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How to check for specific text within text in a cell

Postby cid » Sun May 12, 2019 2:22 am

This seemed to work, thanks!
OpenOffice 4.1.5 on Window 10
cid
 
Posts: 4
Joined: Sat May 11, 2019 12:42 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests