CODEWITHSUNDEEP
vbaexcel
user7105349
user7105349

Reputation: 13

Excel Concatenate Cells while adding characters and skipping Empty cells

I am not a programmer, but doing Excel work for a small library. We have these fields in an excel sheet:

John  | J   | Smith | BMI   | 123 | 100 |
Sarah |  P  | Crown | ASCAP | 564 | 100 |
Tommy |  T  | Stew  | BMI   | 134 | 100 |
Suzy  | S   | Smith | BMI   | 678 | 50  | 
John  | J   | Smith | BMI   | 123 | 50

What I would like to be able to combine any of the cells (in the same row)into one cell that would read like this:

John J Smith, (BMI), 100%, IPI 123

or

Suzy S Smith, (BMI), 50%, IPI 678  | John J Smith (BMI), 50% IPI 123

I figured out how to use the Concatenate function to do this, but it doesn't skip empty cells, and I get extra "|" or "()" in those spots. I also found the =StringConCat topic, and that works great for skipping, but I can't figure out how to add the extra characters.

Any help would be most appreciated. Thank you!!

EDIT: Thanks for the quick responses so far. I should be more clear -

Sorry that I don't know enough to ask my question as intelligently as I should. Just learning how to do this, and trying to figure it out as I go.

Thanks again!

Upvotes: 0

Views: 699

Answers (1)

Fixer
Fixer

Reputation: 74

For the extra spaces, use substitute to get rid of empties.

So, if your code is =concatenate(A1,B1,C1) and your 'empty spaces' are "| " then edit your formula to become =substitute(concatenate(A1,B1,C1),"| ","")

You can even stack the substitutes to add more possible 'empties', like " " (two spaces) or the like. =substitute(substitute(concatenate(A1,B1,C1),"| ","")," ","")

Upvotes: 1

Related Questions