[Solved] Search and Replace Text

Discuss the spreadsheet application

[Solved] Search and Replace Text

Postby Moon Lit Nights » Mon Apr 08, 2019 4:16 pm

Hi,

I am trying to search content in several cells (their info changes based on another cell) and would like to replace the original text with 1 [EDIT: or any other number other than 0] if the word is found and 0 if the word is not found. These numbers are then summed together. It's rough for me to put into words so I am attaching a sheet.

Any help would be greatly appreciated.
Last edited by Moon Lit Nights on Mon Apr 08, 2019 4:41 pm, edited 1 time in total.
OpenOffice 3.1 on Windows
Moon Lit Nights
 
Posts: 28
Joined: Sun Jan 31, 2016 6:44 am

Re: Search and Replace Text

Postby FJCC » Mon Apr 08, 2019 4:31 pm

You can use a formula like
Code: Select all   Expand viewCollapse view
=NOT(ISERROR(FIND("Hello";$B2)))

or
Code: Select all   Expand viewCollapse view
=NOT(ISERROR(SEARCH("Hello";$B2)))

FIND() is case sensitive, SEARCH() is not.
These will return TRUE or FALSE. You can format the cells as integer numbers if you want to see 1 and 0 instead of TRUE and FALSE. TRUE has a value of 1 and FALSE has a value of 0, so the sum will work in either case.
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: 7257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Search and Replace Text

Postby Moon Lit Nights » Mon Apr 08, 2019 4:41 pm

Worked perfectly, thank you so very much!
OpenOffice 3.1 on Windows
Moon Lit Nights
 
Posts: 28
Joined: Sun Jan 31, 2016 6:44 am


Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot], r l and 31 guests