Concatenate type function but with a difference

[ninad7]

I would like to Add the data in 2 or 3 or 4 cells into a single cell and Concatenate or using the "&" will work fine if the result was in another cell. E.g. if A1 = "the", B1 = "Big" and C1 = "Fight" then Cancatenate or "&" in D1 will give the result "the big fight".


How do I achieve this same result in either A1 or B1 or C1 ?, i.e. if the selected cell is B1 and then I want B1 to be B1 + C1 - How to get this done. The number of cells to Concatenate could range from minimum of 2 upwards.


TIA.


Ninad.

 

[keymaster]

Excel formulas do not work like this. You may want to write a simple macro to achieve the result.

 

[JesseH77]

If you want a formula that you will always concatenate the three adjacent cells that would be fairly easy. That's not what you're asking but might serve as a work around for you. One such formula would be:


=CONCATENATE(OFFSET(INDIRECT(CELL("address")),0,1),OFFSET(INDIRECT(CELL("address")),0,2),OFFSET(INDIRECT(CELL("address")),0,3))


Jesse

 

[ninad7]

@Chandoo: I like the use of the word "simple". Can you help. Or provide a direction towards the macro/VBA.


@Jesse: The cells need not be adjacent as you rightly understood. I'll try what you have posted and report back.

 

[Luke M]

@ninad7


Here's an example macro. Takes all the cells that are selected and concatenates them into the active cell. You can change the delimiter as desired.

Sub ConcCells()

Dim Delim As String

Dim NewWord As String


Delim = "&"


If Selection.Count > 1 Then

For Each c In Selection

NewWord = NewWord & c.Value & Delim

Next c

NewWord = Left(NewWord, Len(NewWord) - Len(Delim))

ActiveCell = NewWord


End If

End Sub

 

[ninad7]

Hi Luke,


Thanks. Solves a lot of trouble for me.


Ninad.