Search for multiple substrings and return different values [Solved/Closed]

Question

Search for multiple substrings and return different values [Solved/Closed]

Report

jotied

Posts: 3
Registration date: Wednesday September 9, 2015
Status: Member
Last seen: September 9, 2015

- Sep 9, 2015 at 05:20 PM
jotied

Posts: 3
Registration date: Wednesday September 9, 2015
Status: Member
Last seen: September 9, 2015

- Sep 9, 2015 at 09:24 PM

I have a cell I2 which contains long string. I want to find out the following:
If string in I2 contains "month", then return "Monthly",
If string in I2 contains "year", then return "Yearly",
Else, return "other".

I am using the following formula, but I'm just getting #VALUE.
=IF(OR(ISNUMBER(SEARCH("month",I2)),"Monthly",(ISNUMBER(SEARCH(year,I2)))),"Yearly","Other")

I would appreciate any help or if you can please point out what I may be doing wrong. Thanks a bunch!

Search for multiple substrings and return different values
Excel/VBA - A Find search with multiple return - How-To - Excel
If cell contains multiple text then return value ✓ - Forum - Excel
Search inside an array and return a row? ✓ - Forum - Office Software
Compare multiple cells and return other value ✓ - Forum - Excel
Search word from list, match it copy values next to it ✓ - Forum - Excel

1 reply

Ask a question

Mazzaropi · Accepted Answer · 2015-09-09T20:48:31-0400

Answer 1 / 1

Mazzaropi

Posts: 1837
Registration date: Monday August 16, 2010
Status: Contributor
Last seen: October 17, 2020

136
Sep 9, 2015 at 08:48 PM

jotied, Good evening.

Try to use:

=IF(ISNUMBER(SEARCH("month",I2)),"Montly",IF(ISNUMBER(SEARCH("year",I2)),"Yearly","Others"))

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Report

jotied

Posts: 3
Registration date: Wednesday September 9, 2015
Status: Member
Last seen: September 9, 2015

Sep 9, 2015 at 09:24 PM

Marcílio - That did it! Thank you very much!

For anyone else out there, I also tweaked the formula a bit to get another value. I have two columns, one with "payment amount- column I" and another with "payment frequency- column V". I was ultimately trying to find the average revenue per user per month. To get that, I just had the formula print the value from V column in case the payment frequency was monthly. If payment frequency was yearly, I had the formula divide the value in V column by 12. See below for example and I hope it helps someone else out.

=IF(ISNUMBER(SEARCH("month",I2)),V2,IF(ISNUMBER(SEARCH("year",I2)),V2/12,"Other"))

p.s. I'm starting the formulas in row 2, thus using 2 on all columns (e.g. I2, V2).

Your reply