concatenation with Alt + Enter

[VARGEESH]

Hi All,


I am using the concat code of Chandoo to concatenate a range of cells.


It's very useful for me.


The result of the code is like this.


Eg:-

A1= APPLE

A2= ORANGE

A3= BANANA


while executing the concat code the result will be like this. [=CONCAT(A1:A3),,)]


APPLE,ORANGE,BANANA


But the result shoud be like this in one cell like this.(In a single cell, like using Alt + Enter)


APPLE

ORANGE

BANANA


The code which is i am using:


Function concat(useThis As Range, Optional delim As String) As String

' this function will concatenate a range of cells and return one string

' useful when you have a rather large range of cells that you need to add up

Dim retVal, dlm As String

retVal = ""

If delim = Null Then

dlm = ""

Else

dlm = delim

End If

For Each cell In useThis

if cstr(cell.value)<>"" and cstr(cell.value)<>" " then

retVal = retVal & cstr(cell.Value) & dlm

end if

Next

If dlm <> "" Then

retVal = Left(retVal, Len(retVal) - Len(dlm))

End If

concat = retVal

End Function


Kindly do the needful.


Thanks,

Vargeesh

 

[DonMinter]

Replace the comma with an ascii line feed (value = 10). Set your dlm value to =char(10).


'If delim = Null Then

dlm = char(10)'


I think that's the new code, but I'm not very vba'ish. char(10) is the formulaic way of writing it.


This will do your method if NO delimiter is requested. If you put in a comma (like your example) it will still work the same way.


Note: Coders, if my reserved word or its usage is wrong, please correct.


Thanks,

Don

 

[Hui]

VARGEESH


Firstly, Welcome to the Chandoo.org Forums


Did you try entering a Carriage Return as the seperator character


=CONCAT(A1:A3),Char(10))


Then goto the cell Properties and ensure that Word Wrap is enabled

 

[VARGEESH]

Dear Hui,


Thank you so much.


It's working perfectly.


Warm Regards,

Vargeesh

 

[VARGEESH]

Hi Don,


Thank you for your advice.


Have a nice day.


Warm Regards,

Vargeesh