# Adding Data From Multiple Cells to One Cell

#### meppwc

##### Active Member
I don't know if there is a way to do this, but i suspect that someone may have had this need

My column A represents "Complete Address"
Column B is the "Number and Street"
Column C is the "Dock"
Column D is the "City"
Column E is the "State"
Column F is the "ZipCode"

I am trying to make column A combine all of these cells to equate to one address
For Example:

Column B = "800 N Bradley"
Column C = "Dock C"
Column D = "Landon"
Column E = "MO"
Column F = "60435"

Column A =
Dock C
Landon, MO 60435

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Tenal

##### New Member
Hello,
You need to use the concatenate function. i.e
=concatenate(B2," ",C2, " ",D2," "... Etc
the " " replaces a space otherwise the text merges.
If you want each cell to be on a new line, replace " " with CHAR(10) and select wrap text.

#### meppwc

##### Active Member
I tried this formula but it returned #NAME error
Maybe because the data in B, C, D, E, F are the result of a VLOOKUP

=AF&CHAR(10)&AG8&CHAR(10)&CHAR(10)&AH8&" "&AI8&"."&AJ8

#### meppwc

##### Active Member
Using the formula below it fixe d the #NAME issue but did not skip lines

=CONCATENATE(AF9,CHAR(10),AG9,CHAR(10),AH9,", ",AI9,". ",AJ9)

800 N BradleyDockC Landon, MO. 60435

#### meppwc

##### Active Member
Sorry guys..............my last formula now works after turning on word wrap

#### meppwc

##### Active Member
If there is no value for the "Dock" (column C), is there a way to omit that line"

All of my addresses do have number/street, city, state, zip
But not all have a dock

The formula in cell AE217 does not have dock associated with the address
This is the formula in AE217 -- =CONCATENATE(AF217,CHAR(10),"Dock ",AG217,CHAR(10),AH217,", ",AI217,". ",AJ217)

Last edited:

Replies
1
Views
143
Replies
1
Views
141
Replies
1
Views
69
Replies
1
Views
115
Replies
0
Views
205

1,148,280
Messages
5,745,829
Members
423,981
Latest member
ph1l

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back