Excel formula to populate Data based on a series or rules run on a different cell in the same worksh-CodePudding

I need to populate a cell with some combination of data from another cell. Here's what my data looks like (sorry I can't post images):Column A = NameColumn B = TeamColumn C = Short Name

I'm trying to populate the Short Name based on a series of rules applied to the Name (column A). I have some of it working. So here's what I'm hoping to get to:

this is the rule that's working: - If the name contains a ":" I'm populating the cell with the first 4 characters after that [IFERROR(B2&""&MID(A2, FIND(":", A2,1) 1,4),"")]. If it doesn't find the :, it's leaving Column C blank. This was working perfectly until a new need was introduced...

So now Column C would be populated based on one of 3 things:

Exactly what's happening now. So if : exists populate column C based on the formula above
If the : doesn't exist I need to inspect Column A for "(ENH)". If (ENH) exists, I need column C populated with everything that's displayed in column A. If it exists, (ENH) will always be the first 5 characters displayed in column A.
If : or (ENH) doesn't exist, Column C stays blank

Using my real data, at the end of the day Column C will display one of the following 3 things, displayed in the order of my rules above:

Gold O&M
(ENH) UI Refactor
BLANK

CodePudding user response：

You can use the following formula to populate the C "Short Name" column:

=IF(ISNUMBER(SEARCH(":", A2)), B2&" "&MID(A2,SEARCH(":", A2) 1, 4), IF(ISNUMBER(FIND("(ENH)", A2)), A2, ""))

This results in the following table:

NameColumn	TeamColumn	Short Name
foobar:O&M	Gold	Gold O&M
(ENH) UI Refactor	whatever1	(ENH) UI Refactor
Flubber	whatever2

Notes:

FIND() is case-sensitive while SEARCH() is not. I used FIND() for "(ENH)" because it seemed as though you wanted an exact match. If "(enh)" should also match, use SEARCH().
ISNUMBER(...) can be used to check whether SEARCH() and FIND() returned a valid index instead of #VALUE!, and thus whether the search text was found. NOT(ISERROR(...)) could also be used.

CodePudding user response：

Based on the little info given without posting samples this would work using Office 365: =IFERROR(B2&" "&LEFT(TEXTAFTER(A2,":",1),5),IF(LEFT(A2,5)="(ENH)",A2,""))

TEXTAFTER checks for a (nth) delimiter and shows the text after that. This is (if you ask me) easier to read than MID. But basically it does the same in this case.

If you need a better answer follow dbc's advice to post data as markdown table.