I have a situation that I need to do a concatenation to add a single quote in the beginning and end of whatever is at column Q, and repeat the value twice by row in column I:

enter image description here

So far I was doing things manually, since Excel was not able to autofill things correctly (maybe I'm doing it wrong as well).


Since I need to do this for 500 different rows, I wanted to do something automatic, my thought process would be do something like:

In Column Y, I got the correct cell I want to access using:

CONCAT("Q") + INT(((ROW(A3)-1)/2))+MOD(ROW(A3)-1;2) 

which is the cell I need to get access, so I was thinking of having a formula like: ACCESS_VALUE(Y2), it would read "Q1" in Y2, and return the "1975351765" value, and I could add the single quote in column I.

Maybe I'm doing the most complicated way, let me know if there's an easier way to do so, any help is appreciated!

Thanks in advance!

1 Answer 1


The problem you are having is related to your layout of two rows sourcing data from one. With some flexibility in your layout it would be 2 cell copied as many as you want.

It seems that you source data is a single vertical list

Would it be acceptable to put you < and >= labels side by side in one row. Then you need one relative addressing formula in each of the two cell. Copy this down for however many rows of source data you have.

The problem you are having is related to your layourt of two row sourcing data from one.

Sample in A1 corner


< >= source value
'123' '123' 123
'234' '234' 234

Formulas (flipped sheet)

< >= source value
"'"&D2&"'" "'"&D2&"'" 123
"'"&D3&"'" "'"&D3&"'" 234
  • That's a good solution, but the problem is that I require to keep that layout because I'll use the excel to upload to a program, but that's a good workaround, thanks for your answer! Sep 21 at 11:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .