We will be migrating from Ask to Discourse on the first week of August, read the details here

# Problem using offset with concat to current cell. [closed]

I want to concatenate the contents of two cells immediately to the left of my current cell, and place the result in my current cell, by using the offset functions. Is this possible? I cannot figure it out. I tried using address(row(),column()) to reference the current cell and offsetting from that value, but just get error 504.

Formula goes into cell L10. concatenate(offset(L10,0,-2),offset(L10,0,-1)) works fine, but I need to remove direct reference to current cell, i.e. L10.

tried replacing L10 with address(row(),column()) .. does not work. also tried cell("address") .. does not work either. Probably to do with result format but I do not know solution. 20 years ago I would have pursued like a dog with a bone, but age has slowed me down and I cannot endure long nights anymore. Any help joyfully received.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-20 16:01:32.271773

Hi @stevenolan, I think there is no way, it's possible use offset e.g. to change a range to sum, but not to join strings. Maybe explaning what you want achieve.

( 2014-04-01 00:36:21 +0200 )edit

Sort by » oldest newest most voted

Thanks guys. Turns out to be a pretty stupid question. It was late at night. I just wanted to concatenate the adjacent two cells to the left of my current cell to my current cell. Example:

J10 = "abc" K10 = "def" J20 = "ghi" K20 = "jkl"

originally I had L10 = concatenate(J10,K10) : Result = "abcdef" If I cut and paste L10 to L20 then L20 became concatenate(J10,K10) : Result = "abcdef" I did not want this I wanted L20 to become concatenate(J20,K20) : Result = "ghijkl"

Solution was L10 = concatenate(offset(L10,0,-2),offset(L10,0,-1))

If I now cut and paste L10 to L20 then L20 becomes concatenate(offset(L20,0,-2),offset(L20,0,-1)) : Result = "ghijkl"

So, problem solved.

Again, thank you all.

more
1. Select cell C3
2. Press Ctrl+F3 or choose Insert - Names - Define
3. Press button Add, set Name (for example MyConcat), Range =A1&B1, press Add and OK
4. Use formula =MyConcat anywhere
more