Formula to exclude items in a range of cells

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have a number of cells that I want to concatenate into one cell (merge multiple records into one record). However, I would like to exclude cells that start with the words State or Service Center since these have to be placed into a separate field in the record.

Column A has all the member IDS for our organization. During their time with our company, some members have held local and/or state positions (there is presently multiple records per member but we need to merge everything into one cell so we don't have duplicate member ID's in the list) .

Local positions don't have positions that include the terms State or Service Center - these are State positions. State and Service Center positions should not be included with loc positions.

Here is an example:


In range B1:B4 I have the following 4 items (Range A1:A4 has the same member ID so I have to join rows 1:4 into row 1):

B1:Member
B2:State
B3:Service Center
B4: Board

Contents C1: Member, Board; D1: State; E1: Service Center

However if a member, for example, had Member, Treasurer, President listed I would want all three in C1. Only positions that contain State or Service Center have their own fields.

I need a generic formula that would exclude cell that starts with the terms State or Service Center but include everything else.

Is this possible?

Thank you for your help in advance,

Michael
 
Thank you for the explanation, this is great! I'll try this out when I get back to work tomorrow. I'm sure with these tools i should be able to figure out something that will work for all the scenarios.

I'm still totally lost how get all the spaces in your responses. When I try to create a table by adding spaces or using the tab key my result is a table that is all squished together. I spend a lot of time spacing out my replies but when I post it, the table comes out one big mess. How do you get your responses to appear so nice and clean?

Thank you again,

Michael
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I have a spreadsheet, obviously - I highlight all of it, control C, move to reply box on here, control V
 
Upvote 0
Ah, I had my tables in Excel lined line like you would normally but I just realized I have split the 2nd and 3 columns (Extremely and Interesting) by adding extra columns (to get these 3 columns to look like this I have to use 7 columns and everything is lef alignment when it comes into this format even though I have everything centered in my spreadsheet):

Helpful Extremely Interesting
1 1 1
2 2 2
3 3
4 4
5
6




To get this format, I had to do the following

Helpful in Column A; Column B is blank, Col C contains Extremely, column D Contains 1-6; Column E Interesting, Column F is blank and column G contains 1 and 2.

Interesting how this works, isn't it?

Michael
 
Upvote 0
Even after I did all this it still comes out squished - unless others aren't seeing what I'm seeing. As I look at this I have the headers without spaces and the numbers are squished together. Maybe there is a feature I have to turn on from my end?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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