Concatenate Text... with spaces
I'm having a problem, and I don't know if there's a solution in the form of
an Excel function. I believe I could write a VB Macro to accomplish what I need to do, but I can't use macros for this application. I have a Dataset that looks like this: John Smith <blank cell Jane Doe John Doe <blank cell Jane Smith I need it to concatenate as John Smith, Jane Doe, John Doe, Jane Smith I have the MCONCAT function, which works, but it does not solve the blank cell problem. I have also tried the formula suggested by Toothless Mama in the past, but that doesn't help me with the space. Any suggestions would be appreciated! Thanks! |
Concatenate Text... with spaces
Leslie,
MCONCAT does solve the spaces problem =MCONCAT(A1:A6," ") Returns John Smith Jane Doe John Doe Jane Smith Mike "Leslie W." wrote: I'm having a problem, and I don't know if there's a solution in the form of an Excel function. I believe I could write a VB Macro to accomplish what I need to do, but I can't use macros for this application. I have a Dataset that looks like this: John Smith <blank cell Jane Doe John Doe <blank cell Jane Smith I need it to concatenate as John Smith, Jane Doe, John Doe, Jane Smith I have the MCONCAT function, which works, but it does not solve the blank cell problem. I have also tried the formula suggested by Toothless Mama in the past, but that doesn't help me with the space. Any suggestions would be appreciated! Thanks! |
Concatenate Text... with spaces
Try this:
=SUBSTITUTE(MCONCAT(IF(A1:A6<"",A1:A6&", ","")),",","",COUNTA(A1:A6)) ctrl+shift+enter, not just enter "Leslie W." wrote: I'm having a problem, and I don't know if there's a solution in the form of an Excel function. I believe I could write a VB Macro to accomplish what I need to do, but I can't use macros for this application. I have a Dataset that looks like this: John Smith <blank cell Jane Doe John Doe <blank cell Jane Smith I need it to concatenate as John Smith, Jane Doe, John Doe, Jane Smith I have the MCONCAT function, which works, but it does not solve the blank cell problem. I have also tried the formula suggested by Toothless Mama in the past, but that doesn't help me with the space. Any suggestions would be appreciated! Thanks! |
All times are GMT +1. The time now is 02:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com