Adding Data From Multiple Cells to One Cell

meppwc

Active Member
Joined
May 16, 2003
Messages
469
Office Version
  1. 365
Platform
  1. Windows
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 =
800 N Bradley
Dock C
Landon, MO 60435
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Tenal

New Member
Joined
Sep 10, 2015
Messages
5
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
Joined
May 16, 2003
Messages
469
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 16, 2003
Messages
469
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 16, 2003
Messages
469
Office Version
  1. 365
Platform
  1. Windows
Sorry guys..............my last formula now works after turning on word wrap
 

meppwc

Active Member
Joined
May 16, 2003
Messages
469
Office Version
  1. 365
Platform
  1. Windows
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:

Forum statistics

Threads
1,144,374
Messages
5,723,983
Members
422,529
Latest member
mbilal429

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top