EXCEL - if cell does not contain specific text, formula?

Question

Hello. What is the formula I should use to find cells in column A that contain 'accounts'. If YES, column B would be a blank cell. If NO, A would repeat in column B.

Answer 1

Hello OHRW03,

Welcome to the Microsoft Community.

Have a try for this formula:

=IF(ISNUMBER(SEARCH("accounts", A1)), "", A1)

Note: A1 is my assumed cell of your source text, from your image it seems Row 1 is a title and your data starts from Row 2. You can replace it with your actual cell.

This formula checks if the word ‘accounts’ is present in each cell of column A. If it is, it leaves the corresponding cell in column B blank. If not, it repeats the content from column A into column B.

Here’s a step-by-step guide:

1. Select cell B1.
2. Enter the formula: =IF(ISNUMBER(SEARCH("accounts", A1)), "", A1).
3. Press Enter.
4. Drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to other cells in column B.

This should give you the desired result as shown in your image.

Should any uncertainties arise subsequently, please feel free to seek assistance once again.

Best Regards,

William.Y | Microsoft Community Support Specialist

Answer 2

A	B	C	D	E	F	G
Paul		P	A	U	L
Peter		P	E	T	E	R
James		J	A	M	E	S

what formula can I use whenever a name change in A the spelling will automatically changed

Answer 3

it did not work

Answer 4

=MID(A2,SEQUENCE(,LEN(A2)),1)

or

=UPPER(MID(A2,SEQUENCE(,LEN(A2)),1))