Insert Data based on Partial contents of adjacent cell

[hkydad]

Hi,

I apologize if this question has been answered but I wasn't able to locate the thread.

What I am trying to do is insert a formula that looks at the adjacent cell to see if the text includes "-Ann" or "-SANN" and then enter ABCD or EFGH in the adjacent cell.

So if column B includes "-ANN" then "ABCD" should appear in column C. If column B includes "-SANN" then "EFGH should appear in column C.

Thank you.

 

[Fluff]

How about

=IF(ISNUMBER(SEARCH("-ann",B2)),"abcd",IF(ISNUMBER(SEARCH("-sann",B2)),"efgh",""))

 

[hkydad]

How about

=IF(ISNUMBER(SEARCH("-ann",B2)),"abcd",IF(ISNUMBER(SEARCH("-sann",B2)),"efgh",""))

Hi...I entered the formula '=IF(ISNUMBER(SEARCH("-ann",B2)),"abcd",IF(ISNUMBER(SEARCH("-sann",B2)),"efgh",""))' and the only value being returned is abcd. Column B will have items in it that end in either -ann or -sann.

Thanks,

 

[Fluff]

In that case can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[hkydad]

Here is my mini sheet

Excel Formula.xlsx
	A	B
1
2	Doc Code	Company Name
3	Column B should be A-BCD if column A includes "-ANN" Column B should be E-FGH if column A includes "-SANN"
4
5
6	ABC-SANN
7	TDP-ANN
8	DIS-SANN
9	ESP-ANN
10	CAR-ANN
11	ACAN-ANN
12	ADIS-SANN
13	AESP-SANN
14	ATDP-SANN
Sheet1

 

[Fluff]

Thanks for that. The formula I suggested works on that data

+Fluff 1.xlsm
	A	B
1	Doc Code	Company Name
2
3
4
5	ABC-SANN	efgh
6	TDP-ANN	abcd
7	DIS-SANN	efgh
8	ESP-ANN	abcd
9	CAR-ANN	abcd
10	ACAN-ANN	abcd
11	ADIS-SANN	efgh
12	AESP-SANN	efgh
13	ATDP-SANN	efgh
14
Data

Cell Formulas
Range	Formula
B2:B13	B2	=IF(ISNUMBER(SEARCH("-ann",A2)),"abcd",IF(ISNUMBER(SEARCH("-sann",A2)),"efgh",""))

 

[hkydad]

Thank you for checking the formula. For whatever reason it wasn't working when I first tried it but now it is working. Thank you so much.

 

[Fluff]

Glad it's sorted & thanks for the feedback.