Concatenate UDF help please
Hi
I need some help creating a UDF that concatenates a RANGE of cells as follows:- 1. If the cell is BLANK, do not concatenate it 2. If the cell is NOT blank, add a BULLET and SPACE to the start of the text, concatenate it and add a <CR to the end. This is to create a bulleted list of non-blank cells in a range, with a <CR between each entry. so if the range (a single column of data) looks like: <blank hello world <blank <blank more data <blank end <blank I would end up with a text string as follows: — hello world — more data — end Can anyone please help? Many Thanks in advance... NR |
Concatenate UDF help please
Function splice_um(rr As Range) As String
Dim s As String s = "" bullet = ". " cr = Chr(10) For Each r In rr If Not IsEmpty(r) Then s = s & bullet & r.Value & cr End If Next splice_um = s End Function Used a period for a bullet. Text wrapping must be on to see the column. -- Gary''s Student - gsnu200721 |
Concatenate UDF help please
On May 14, 5:47 pm, Gary''s Student <snip wrote some good code:
Gary''s Student - gsnu200721 Perfect - thanks so much :oş NR |
Concatenate UDF help please
Try this. Assume data is in column A
Sub merge_cells() Lastrow = Cells(Rows.Count, "A").End(xlUp).Row ConcatenateWord = "" RowCount = 1 For NextRow = 2 To Lastrow If IsEmpty(Cells(NextRow, "A")) Then If ConcatenateWord < "" Then Cells(RowCount, "A") = "€¢ " + ConcatenateWord RowCount = RowCount + 1 ConcatenateWord = "" End If Else ConcatenateWord = ConcatenateWord + " " + Cells(NextRow, "A") Cells(NextRow, "A") = "" End If Next NextRow If ConcatenateWord < "" Then Cells(RowCount, "A") = "€¢ " + ConcatenateWord End If End Sub "N Ramsay" wrote: Hi I need some help creating a UDF that concatenates a RANGE of cells as follows:- 1. If the cell is BLANK, do not concatenate it 2. If the cell is NOT blank, add a BULLET and SPACE to the start of the text, concatenate it and add a <CR to the end. This is to create a bulleted list of non-blank cells in a range, with a <CR between each entry. so if the range (a single column of data) looks like: <blank hello world <blank <blank more data <blank end <blank I would end up with a text string as follows: — hello world — more data — end Can anyone please help? Many Thanks in advance... NR |
All times are GMT +1. The time now is 11:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com