Merging cells with no-adjacent data
I want to be able to combine the contents of a range of cells into one cell.
Using the concatenate command includes spacing and/or characters for blank cells. ex: , , , , John Smith, , Joe Frazier, , etc. Using the substitute trim commands puts excess characters inside cell values ex: John, Smith, Joe, Franzier etc. I want to be able to take the contents of non-blank cells in a row (15 columns) and combine them with a space and comma between the non blank cell values. Please help! |
Merging cells with no-adjacent data
try this function:
in cell put =merge(A1:H1) Function merge(inrng As Range) As String For Each cell In inrng If Application.And(Len(cell) 0, cell < " ") Then merge = merge & cell.Value & ", " End If Next merge = Left(merge, Len(merge) - 2) End Function "crazymfr" wrote: I want to be able to combine the contents of a range of cells into one cell. Using the concatenate command includes spacing and/or characters for blank cells. ex: , , , , John Smith, , Joe Frazier, , etc. Using the substitute trim commands puts excess characters inside cell values ex: John, Smith, Joe, Franzier etc. I want to be able to take the contents of non-blank cells in a row (15 columns) and combine them with a space and comma between the non blank cell values. Please help! |
Merging cells with no-adjacent data
how do I get VBA to interact with Excel?
I dont have a lot of experience in VBA. "Toppers" wrote: try this function: in cell put =merge(A1:H1) Function merge(inrng As Range) As String For Each cell In inrng If Application.And(Len(cell) 0, cell < " ") Then merge = merge & cell.Value & ", " End If Next merge = Left(merge, Len(merge) - 2) End Function "crazymfr" wrote: I want to be able to combine the contents of a range of cells into one cell. Using the concatenate command includes spacing and/or characters for blank cells. ex: , , , , John Smith, , Joe Frazier, , etc. Using the substitute trim commands puts excess characters inside cell values ex: John, Smith, Joe, Franzier etc. I want to be able to take the contents of non-blank cells in a row (15 columns) and combine them with a space and comma between the non blank cell values. Please help! |
Merging cells with no-adjacent data
Alt+F11
then Alt+I Select Module from Menu You will get a blank area (labelled "General") Copy/paste code into module HTH "crazymfr" wrote: how do I get VBA to interact with Excel? I dont have a lot of experience in VBA. "Toppers" wrote: try this function: in cell put =merge(A1:H1) Function merge(inrng As Range) As String For Each cell In inrng If Application.And(Len(cell) 0, cell < " ") Then merge = merge & cell.Value & ", " End If Next merge = Left(merge, Len(merge) - 2) End Function "crazymfr" wrote: I want to be able to combine the contents of a range of cells into one cell. Using the concatenate command includes spacing and/or characters for blank cells. ex: , , , , John Smith, , Joe Frazier, , etc. Using the substitute trim commands puts excess characters inside cell values ex: John, Smith, Joe, Franzier etc. I want to be able to take the contents of non-blank cells in a row (15 columns) and combine them with a space and comma between the non blank cell values. Please help! |
All times are GMT +1. The time now is 10:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com