Computer and Software Support


Join Data

You can join numerous cells together into one cell. You can even get the information from other worksheets and workbooks. Below is information on a spreadsheet.

  • On worksheet called Name has a person's name John Doe on Cell A1.
  • On worksheet called street is John Doe's street address located at 13 Nowhere ave. It is located on cell B3.
  • On worksheet called city is John Doe's city located at Nashville and it is located on cell C9.
  • On worksheet called state is John Doe's state located at Tennessee and is located on cell C9.
  • On worksheet called zip is John Doe's zip code which is 37214 and is located at cell D9.

In this example I want all the information to appear at cell A1 on worksheet Full. When I do it right, it will look like the following.

John Doe 13 Nowhere ave Nashville Tennessee 37214

Below is will be the different parts of the formula to do the above address.

  • equal sign ( = ) - will indicate that this is a formula that your typing.
  • Name - is the name of the first worksheet. It tells Excel to look at the worksheet called Name
  • exclamation mark ( ! ) = Tells Excel that an address in the worksheet is following
  • Address - Is the exact location on the worksheet the information is on which is A1
  • So far the formula looks like this  =Name!A1 and the cell would show John Doe
  • &" "& - tells Excel that you are adding other cells to the formula.

 

Note: If you want a space between the name and the street address, put a space in between the two "" such as this &" "&. If you don't want a space then type &""&

Note:  You can also add text between the "" if you wanted. If you wanted to have it say the words 'Street address is'  you would put &" Street Address is"&. Then when you would see John Doe Street address is 13 Nowhere ave. the Street Address is will be part of the formula and will not change. If John Doe moves later, you just have to adjust his address in the worksheets provided.

  • Then you will basically repeat the above to include each part of the address you want to show.
  • street - is the name of the second worksheet. It tells Excel to look at the worksheet called street
  • exclamation mark ( ! ) = Tells Excel that an address in the worksheet is following
  • Address - Is the exact location on the worksheet the information is on which is B3.
  • So far the formula looks like this  =Name!A1&" "&street!B3 and the cell would show John Doe 13 Nowhere ave
  • &" "& - tells Excel that you are adding other cells to the formula.

Note: If you want a space between the Street and the city, put a space in between the two "" such as this &" "&. If you don't want a space then type &""&

Note:  You can also add text between the "" if you wanted. If you wanted to have it say the words 'The city is'  you would put &" The city is"&. Then when you would see John Doe Street 13 Nowhere ave The city is Nashville. 'The city is' will be part of the formula and will not change. If John Doe moves later, you just have to adjust his address in the worksheets provided and it will not affect the formula.

  • Then you will basically repeat the above to include each part of the address you want to show.
  • city - is the name of the third worksheet. It tells Excel to look at the worksheet called city
  • exclamation mark ( ! ) = Tells Excel that an address in the worksheet is following
  • Address - Is the exact location on the worksheet the information is on which is C9.
  • So far the formula looks like this  =Name!A1&" "&street!B3&" "&city!C9 and the cell would show John Doe 13 Nowhere ave Nashville
  • &" "& - tells Excel that you are adding other cells to the formula.

Note: If you want a space between the city and the state, put a space in between the two "" such as this &" "&. If you don't want a space then type &""&

Note:  You can also add text between the "" if you wanted. If you wanted to have it say the words 'The state is'  you would put &" The state is"&. Then when you would see John Doe Street 13 Nowhere ave Nashville The state is Tennessee. 'The state is' will be part of the formula and will not change. If John Doe moves later, you just have to adjust his address in the worksheets provided and it will not affect the formula.

  • Then you will basically repeat the above to include each part of the address you want to show.
  • state - is the name of the fourth worksheet. It tells Excel to look at the worksheet called state
  • exclamation mark ( ! ) = Tells Excel that an address in the worksheet is following
  • Address - Is the exact location on the worksheet the information is on which is C9.
  • So far the formula looks like this  =Name!A1&" "&street!B3&" "&city!C9&" "&state!C9 and the cell would show John Doe 13 Nowhere ave Nashville Tennessee
  • &" "& - tells Excel that you are adding other cells to the formula.

Note: If you want a space between the city and the state, put a space in between the two "" such as this &" "&. If you don't want a space then type &""&

Note:  You can also add text between the "" if you wanted. If you wanted to have it say the words 'The zip code is'  you would put &" The zip code is"&. Then when you would see John Doe Street 13 Nowhere ave Nashville Tennessee. 'The zip code is' will be part of the formula and will not change. If John Doe moves later, you just have to adjust his address in the worksheets provided and it will not affect the formula.

  • Then you will basically repeat the above to include each part of the address you want to show.
  • zip - is the name of the fourth worksheet. It tells Excel to look at the worksheet called zip
  • exclamation mark ( ! ) = Tells Excel that an address in the worksheet is following
  • Address - Is the exact location on the worksheet the information is on which is D9.
  • So far the formula looks like this  =Name!A1&" "&street!B3&" "&city!C9&" "&state!C9&" "&zip!D9 and the cell would show John Doe 13 Nowhere ave Nashville Tennessee 37214
  • &" "& - tells Excel that you are adding other cells to the formula.

You can download an example spreadsheet to see how this works. Click here to download the excel spreadsheet. Click on the worksheet Full and then modify the various cells to see what changes on worksheet Full. You will notice that you can't alter worksheet Full but you can alter the other worksheets. You will also see the information on Full change as the referenced cells are altered.