Thank you, Somendra! I worked with the method you shared and developed a workaround for this problem:
_______________________________________________________
| Since F9 replaces formulas with values, if your original data changes, then you must re-write the |
| CONCATENATE(TRANSPOSE(…)) again. |
First I get the addresses I need:
SUBSTITUTE(ADDRESS(ROW(B2),COLUMN(B2)),"$","")
The values I want to concatenate are in cells B2 to J2, so above formula gives:
___B__C___D__E__F__G__H__I___J_
3|
4| B2 C2 D2 E2 F2 G2 H2 I2 J2
Type =B4:J4 Press F9 to get:
={"B2","C2","D2","E2","F2","G2","H2","I2","J2"}
Replace { & } with ( and ), replace "," with , remove remaining " - THEN
put CONCATENATE in front and you have a reusable formula that you can drag down to concatenate multiple rows.