CONCATENATE usage

[Neitrino]

Hello,

I have two question

1) I have a huge column... and I want contents of each cell to be taken and put in one single cell .... how can I employ the "concatenate" function to select huge range ?

2) How do I add some certain character to the end of each cell in range?
For example is I have: text1, text2, text3 and I want to get text1A, text2A, text 3A and etc...

Thanks

 

[Scott Huish]

Use the following UDF:

Function ConcatSpecial(r As Range, delimiter As String, Optional appendchar As String) As String
Dim c As Range, s As String
For Each c In r
    If c <> "" Then s = s & c & appendchar & delimiter
Next
s = Trim(s)
ConcatSpecial = Left(s, Len(s) - 1)
End Function

Then in the spreadsheet, you can do something like this:

=ConcatSpecial(A1:A1000,", ")

which will concatenate the values of A1:A1000 separated by a comma and a space.

To add a character(s) to each string, use the same function, but add the append character to the end of the formula:

=ConcatSpecial(A1:A1000,", ","A")

 

[Scott Huish]

Excel Workbook
	A	B	C	D
1	text1			text1, text2, text5
2	text2			text1A, text2A, text5A
3
4	text5
Sheet1

 

[Neitrino]

Dear Hotpepper,

Thanks for ur help it was just great and it saved me making time consuming typing... may u please advise me some book to learn that stuff, I have no idea of programming so some simple book for dummies... to learn quickly in short time...


Thanks much

 

[Scott Huish]

Don't have any book recommendations, but there is a book called
Excel VBA Programming For Dummies

 

[Neitrino]

Dear Hotpepper,
Pls advise me what I do wrong....

Function CellA1equals15()
Range("A1") = "15"
End Function

Wherever I shall type function CellA1equals15 I want that in cell A1 number fifteen to appear... but it doesnt waork

I hit my head against the wall pls advise

Thank you

 

[Scott Huish]

Functions can't return values to other cells. You need to run a regular macro for that.

Sub A1equals15
Range("A1")=15
End Sub

 

[Neitrino]

Dear Hotpepper,

In your scrip you type: Dim c As Range... whats the diffrerence in you had stated just c As Range and why this Dim c As Range isout parenthesis?


Thank you

 

[Neitrino]

Dear Hotpepper,
I am sorry if I bother you with my questions but seems with your help I am getting into the drill.....

So next question: as I understand there are Objects, Obejcts can have properties (Object property) and Objects can have methods (Object method) and when I want to assign a method or property to a object i write them together but dot is used between them:

Range("A1").Value or Range("A1") .ClearContents for example.

And basically whole VBA (at least bulk of VBA in excel) job is operation with these three items.

Where can i find the list of all Objects, Object properties and Object methods which already exisit and thoese will be bricks for my VBA code?

Thanks very much

 

[Scott Huish]

The VBA help file.