# Copying multiple cells to one cell including blanks

#### steve the excel guy

##### Board Regular
I want a formula in one cell that will include all the data from other cells including blanks like so:

A1 A2 A3 A4 A5
1 2 4 5

In the above example, there is one number in each cell except for A3.

I want a formula in B1 that will return "12 45" based on the above scenario. In my current arrangement, it will only produce "1245" with no spaces.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### SteveO59L

##### Well-known Member
But if the cell is blank, then it isnt a space

Last edited:

#### SteveO59L

##### Well-known Member
You could do something along the lines of

=IF(A1=""," ",A1)&IF(B1=""," ",B1)&IF(C1=""," ",C1)&IF(D1=""," ",D1)&IF(E1=""," ",E1)

#### steve the excel guy

##### Board Regular
Ok that does work. I'm wondering if there's a shorter way in case I wanted to expand this to a larger scale like A1 thru AZ1 or whatever...
If all else fails, I suppose I could take the time and enter that "=IF(A1=""," ",A1)" for every cell.

Thank you.... and I'm glad you noticed concatenate doesn't work.

#### steve the excel guy

##### Board Regular
Here's what it looks like going only to Z1.... (wipes sweat from brow)

=IF(A27=""," ",A27)&IF(B27=""," ",B27)&IF(C27=""," ",C27)&IF(D27=""," ",D27)&IF(E27=""," ",E27)&IF(F27=""," ",F27)&IF(G27=""," ",G27)&IF(H27=""," ",H27)&IF(I27=""," ",I27)&IF(J27=""," ",J27)&IF(K27=""," ",K27)&IF(L27=""," ",L27)&IF(M27=""," ",M27)&IF(N27=""," ",N27)&IF(O27=""," ",O27)&IF(P27=""," ",P27)&IF(Q27=""," ",Q27)&IF(R27=""," ",R27)&IF(S27=""," ",S27)&IF(T27=""," ",T27)&IF(U27=""," ",U27)&IF(V27=""," ",V27)&IF(W27=""," ",W27)&IF(X27=""," ",X27)&IF(Y27=""," ",Y27)&IF(Z27=""," ",Z27)

#### SteveO59L

##### Well-known Member
Or, with a bit of code

Dim c As Range, rng
Set rng = Range("a1:az1")
For Each c In rng
If IsEmpty(c) Then

Range("ba1").Value = Range("ba1").Value & "*"
Else
Range("ba1").Value = Range("ba1").Value & c.Value
End If
Next c
Range("BA1").Select
Cells.Replace What:="~*", Replacement:=" ", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

#### steve the excel guy

##### Board Regular
Hmm, ok... so where you say BA1.. what is that doing? Is that similar to what I was trying to do for A2?

Either way, I pasted this code into a module and it doesn't do anything. Perhaps I am to run it like a macro but I am not versed enough yet to make the appropriate changes to call it a name and make it run.

#### SteveO59L

##### Well-known Member
Tools, Macro, Record New Macro.

Then Stop Recording

Tools Macro, Macros, Edit

Paste the code between you newly created Sub/End Sub lines

then Tools Macro, Macros, Run

if you want the result to be in A2 then simply change BA1 to A2

#### steve the excel guy

##### Board Regular
lol... I went through all that it kept the spaces/blanks out. Hey that's ok. I'll just pasting the first formula that you showed me. Thank you for your help.

Replies
7
Views
197
Replies
3
Views
207
Replies
1
Views
285
Replies
3
Views
1K
Replies
5
Views
356

1,191,317
Messages
5,985,944
Members
439,991
Latest member
NCWalker

### 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