1Z? Do you mean Z1? Can you post a few rows of sample data (please replace confidential data with dummy text) and mock up what you would like to see as the end result?

cheers, teylyn

Solved

Posted on 2011-10-16

Hi, I have a very long list of data that pertains to various customers and the information is scatter from cell 1A all the way to 1Z. Excel 2003. I would like to keep the company name in column A but expand column B to store all of the other data that is currently under different columns. I have tried different things (wrap cell), but none of them have worked. It is a tremendous amount of data and I would hate to have to re-type it. Is there any way to do this without any programing knowledge? Thank you for your help.

24 Comments

1Z? Do you mean Z1? Can you post a few rows of sample data (please replace confidential data with dummy text) and mock up what you would like to see as the end result?

cheers, teylyn

=C1&", "&E1&", "&F1&", "&G1&", "&H1&", "&I1&", "&J1&", "&K1&", "&L1&", "&M1&", "&N1

etc. Copy down. Then copy column B and use Paste Special > Values to paste just the results. Then you can delete the other columns.

cheers, teylyn

I tried doing what you suggested but Excel didn't like it. Look this over and tell me if I did something wrong.

Thanks

QKees-Screens.jpg

from what I can see in the formula in the error message, it ends with an opening double quote sign that does not get closed. If Z1 is the last cell to be appended, then Z1 should be the last entry in the formula.

Let's take a closer look at what the formula does:

=C1&", "&D1

The contents of cell C1 is combined with a comma and a space and then the content of D1 is appended to that.

The & sign is the operator to append text values.

The ", " is a piece of text, consisting of a comma and a space character. All text must be enclosed in double quotes in a formula.

In your formula I see no text between the "" signs. If you do not want to have any separators between the cell contents, then you can just use

=C1&D1&E1 etc.

cheers, teylyn

"

In your formula I see no text between the "" signs."

Could you please explain more fully? Thank you for your time.

=C1&", "&D1&", "&E1 etc.... but later on it just shows &I1&""&J1&""&L1

There is nothing enclosed in the double quotes, no comma and no space character. If you want to separate the contents of cell I1 and J1 and L1 with a comma and a space, then you need to use these characters between the quote signs

QKees-Screens.jpg

=C1&", "&D1

Hit Enter. Does that work as expected? If so, edit the formula and add the next cell concatenation

=C1&", "&D1&", "&E1

Hit Enter. Check the result.

Attached is a file with the working formula concatenating all cells from C1 to Z1 separated by a comma and a space.

cheers, teylyn

Book1.xls

QKees-Screens.jpg

Does that answer your question?

cheers, teylyn

In my example file, the formula is in A1. Copy cell A1 (not the formula in the formula bar, but the cell itself).

Click another cell, for example A2, and use Paste Special > Values.

A2 now contains the text. No formula.

So, in your file, does the formula do what you need it to do? If so, click the cell with the formula, copy the cell, select another cell and use Paste Special > Values.

Three simple steps. Please tell me where you get stuck.

cheers, teylyn

If I am successful on the other computer, if I understand you correctly, once I create the formula in cell A1, I copy the formula from cell A1 and use paste special - values and all of the content that is now in cells B1 through Z1 will be consolidated into cell A2, separated by a comma. Is that correct? Since you already created the formula, is there a way I can use your formula?

Have you tried my suggestions from above to start with small steps? This is definitely user error when entering the formula.

Start with just

=C1&", "&D1

Does that work? If so, add the next cell

=C1&", "&D1&", "&E1

Does that work?

Do you see that the last element in the formula is a cell reference, not a & sign and not a " sign?

The elements of the concatenation are not

"&F1&" or "&G1&"

This thinking will get you into trouble.

The elements are

C1 and D1 and E1. In between these is a comma and a space, wrapped in double qotes -- ", " -- Two kinds of elements: one is a cell reference, one is a text with quote signs. These elements alternate

C1 ", " D1 ", " E1

To put them together, the Ampersand sign & is used. It is like the + sign in 1+2+3

C1&", "&D1&", "&E1

Start it off with a = sign and you have a proper formula

=C1&", "&D1&", "&E1

```
Public Function BCF(parmRangeAreas As Range, Optional ByVal parmDelim As String, _
Optional ByVal parmConcatByCol As Boolean) As String
'BCF is a Better Concatenation Function
'Process: Iterate Areas; transfer the area cell data to a
' variant array; copy items to string array,
' concatenated and returned.
'Parameters:
' parmRangeAreas is a parentheses-enclosed range area and the BCF()
' parmDelim is an optional parameter allowing you to add a
' delimiter string between the concatenated cell values.
' parmConcatByCol is an optional parameter allowing you to control the
' order (by row, by column) that area cells are added to the vector
Dim rngArea As Range
Dim lngNext As Long
Dim lngRow As Long
Dim lngCol
Dim lngRowCount As Long
Dim lngColCount As Long
Dim varCells() As Variant
Dim strCells() As String
Dim varItem As Variant
ReDim strCells(1 To parmRangeAreas.Count)
For Each rngArea In parmRangeAreas.Areas
varCells = rngArea.Value
If parmConcatByCol Then
For Each varItem In varCells
lngNext = lngNext + 1
strCells(lngNext) = varItem
Next
Else
lngRowCount = UBound(varCells, 1)
lngColCount = UBound(varCells, 2)
For lngRow = 1 To lngRowCount
For lngCol = 1 To lngColCount
lngNext = lngNext + 1
strCells(lngNext) = varCells(lngRow, lngCol)
Next
Next
End If
Next
BCF = Join(strCells, parmDelim)
End Function
```

>> But if you would kindly look back to your earlier sample you will see that you did not put a space between text and the separators. After your last BIG sample, where you said "YOU NEED TO THINK LIKE THIS" is when you first showed me the proper way to do it

Not quite. I showed you in several posts how to build the formula step by step.

Just as an off-topic comment to this -- Excel is not a database. The issues you are running into will continue to get deeper and deeper.

So you have now concatenated the data into one cell in the spreadsheet. What happens when you want to pull out all the data for customers that are in zip code 11214? Figure out how many commas is the zip code? How about customers with multiple addresses with only one in the zip code? Do you send to the corporate or the individual address.

There are so many implications for the future that trying to stay small could be the worst thing you can do.

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Calculating tiered commission based on plan name and amount | 7 | 32 | |

Array Formula to add additional step | 1 | 21 | |

Have Column "J" in this way when days are pass due and fix load time when using Number Arrange Button. | 11 | 21 | |

trailing spaces all columns | 4 | 42 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**13** Experts available now in Live!