Concatenating Cell References in Excel

 Last reviewed: November 2, 1994
Article ID: Q47492
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

SUMMARY

A cell reference can be concatenated. Ways to use them differ, depending on whether the cell reference is on a macro sheet or a worksheet. Examples on how to use each type follow the general description below.

MORE INFORMATION

Use the text concatenation symbol & (an ampersand) to join cell references. Enclose literal strings with double quotation marks. To refer to the value in another cell or a defined name, use only the cell's reference, or the name, without the quotation marks.

For example, if A5 contains the number 3, and Name is a defined name referring to the number 16, the following is true: 

   Concatenation           Refers to
   -------------           ---------

   "B"&A5                  Cell B3
   "Sheet1.XLS!J"&Name     Cell J16 on Sheet1.XLS
   "R"&Name&"C1"           Cell R16C1 (or A16)
   "R["&Name&"]C1"         16 cells down from where the active cell
                           is, but in column A

Worksheet

Use the INDIRECT function to convert the resulting text string to an actual reference in a worksheet, as follows: 

   =INDIRECT("B"&A5)

Macro

In most cases, you can use the concatenated address just as you would any normal cell reference in a macro function, as follows: 

   =SELECT("R["&Name&"]C1")
However, some functions require an actual reference instead of a text string. In these cases, nest the TEXTREF function inside the other function, as follows: 

   =ROW(TEXTREF("R["&Name&"]C1"))
Another method is to use FORMULA.GOTO in much the same manner. 

   =FORMULA.GOTO("R["&Name&"]C1")

REFERENCES

Online Help, version 5.0, Reference Information, Microsoft Excel Macro Functions Contents

"Function Reference," version 4.0, page 174

"Function Reference," version 3.0, page 93

"Functions and Macros," version 2.x, page 287

KBCategory: kbusage
KBSubcategory:

Additional words: 2.0 2.00 2.01 2.10 3.0 3.00 4.0 4.00 4.0a 4.00a
5.0 5.00 concat

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.