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

Ask Your Question

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

asked 2014-04-01 00:19:06 +0200

stevenolan gravatar image

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 flag offensive 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.

m.a.riosv gravatar imagem.a.riosv ( 2014-04-01 00:36:21 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-04-01 22:36:57 +0200

stevenolan gravatar image

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.

edit flag offensive delete link more

answered 2014-04-01 15:03:57 +0200

JohnSUN gravatar image
  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
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-04-01 00:19:06 +0200

Seen: 1,045 times

Last updated: Apr 01 '14