Replace multiple comma separated values with generic value using IF

phownz

New Member
Joined
Jul 28, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm not sure if I'm taking the correct approach with this one but thought my best option would be an IF statement but not sure on the finer points of Excel and it allowing me to do this.

I have two columns where I would like to pull my data and a third column where I would like the result:

Column AColumn BColumn C
John Doe,Jane Doe,John SmithPaul Smith,James Patrick Smithcolumna,columna,columna,columnb,columnb

Column A and B are firstnames and surnames separated by a comma with no space and will always be different

So I'm trying an IF statement to calculate for each "value" (Firstname Surname) that the formula will create a generic output (in this case "columna" or "columnb", depending on which column the data is in) and replace the values or full names with that column specific generic name.

However I do not know how to build out my formula to include every "value" separated by a comma basically.

So whilst this is my foundation =IF(A2="", "","columna") I'm unsure how to include the range of values and also the best way to include Column B into the mix. The below seems to work at least for one value so I get "columna,columnb" but not sure if this is the most efficient way to do so.

=IF(A2="", "","columna")&IF(B2="", "","columnb")

Any help is appreciated.
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
How about
+Fluff 1.xlsm
ABC
1Column AColumn BColumn C
2John Doe,Jane Doe,John SmithPaul Smith,James Patrick SmithColumn A,Column A,Column A,Column B,Column B
Data
Cell Formulas
RangeFormula
C2C2=IF(A2="","",MID(REPT(","&$A$1,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1),2,1000))&IF(B2="","",REPT(","&$B$1,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))
 
Upvote 0
Hi Fluff,

This works perfectly thank you! I had to tweak the column names slightly to give the desired result but that's my fault for trying to keep the columns as generic as possible for the example.

How about
+Fluff 1.xlsm
ABC
1Column AColumn BColumn C
2John Doe,Jane Doe,John SmithPaul Smith,James Patrick SmithColumn A,Column A,Column A,Column B,Column B
Data
Cell Formulas
RangeFormula
C2C2=IF(A2="","",MID(REPT(","&$A$1,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1),2,1000))&IF(B2="","",REPT(","&$B$1,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))

Column BColumn C
John Doe,Jane Doe,John SmithPaul Smith,James Patrick Smithcolumna,columna,columna,columnb,columnb

Out of curiosity, if I wasn't able to refer to the column names in the naming convention would there be an option whereby I use the IF statement to refer to a particular value? Say if I wanted the outcome to be "apple,apple,apple,banana,banana" in the above output for arguments sake?
 
Upvote 0
You can just change the cell reference to text of your choice.
 
Upvote 0
Got it, thank you. It's a more advanced formula than I'm used to so I will break it down and try to understand what's going on a bit better. Thanks again!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top