?
Solved

Excel Cell Format

Posted on 2011-10-16
24
Medium Priority
?
334 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:camtz
  • 12
  • 10
  • +1
24 Comments
 
LVL 50
ID: 36977211
Hello,

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
0
 
LVL 50
ID: 36977213
In B1 you could use

=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
0
 

Author Comment

by:camtz
ID: 36983116
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50
ID: 36983480
Hello,

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
 
0
 

Author Comment

by:camtz
ID: 36983581
Okay, I believe the mistake that I made is that I ended Z1 with an &.  I definitely want to separate the contents of each cell but what I am not understanding is your comment;
"
In your formula I see no text between the "" signs."

Could you please explain more fully? Thank you for your time.
0
 
LVL 50
ID: 36983611
The formula showing in the screenshot starts off with

=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


0
 

Author Comment

by:camtz
ID: 36983616
Sorry, I missed that.  I will try again. Thanks
0
 

Author Comment

by:camtz
ID: 36988893
Well, I went back and tried it again and as you can see from the screenshot, the computer is not inserting the commas or the space following.  I made sure they were there before I took the screenshot so for some reason the system will not put them in.  If I do it the other way you suggested, does that mean that everything would be put in there without commas?
QKees-Screens.jpg
0
 

Author Comment

by:camtz
ID: 36988899
Please let me know.
0
 
LVL 50
ID: 36989940
Start with smaller steps to catch typos early.

=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
0
 

Author Comment

by:camtz
ID: 36992428
I downloaded the book that you created and this is the way it looks on my computer.  Is this correct?
QKees-Screens.jpg
0
 

Author Comment

by:camtz
ID: 36992438
Screenshot 3
0
 
LVL 50
ID: 36995812
Yes, that's how it looks on my computer as well. And as you can see, the result is correct.

Does that answer your question?

cheers, teylyn
0
 

Author Comment

by:camtz
ID: 36996701
Well, maybe I'm doing something wrong because it is not working.  I actually downloaded your workbook and copied the information on several pages so that I would not lose your data.  I then copied the content of cell B1 to Z1 and used paste special and selected values and attempted to paste the information into cell B1 but what I got was an exact duplicate of the original content that was being copied under each column and not all in column B1 like I want. In other words, instead of everything ending up in B1 separated by a comma, it ended up under each column. Sorry this has been such a problem for you but if I can accomplish this my boss will be very happy so I'll keep trying.
0
 
LVL 50
ID: 36996998
Hang on, I'm not following. I can't quite picture what you want to do.

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

0
 

Author Comment

by:camtz
ID: 36997342
I have tried many times to recreate your formula but I always end up with more " than I need.  I don't know why it does it but I get an error message and it shows the formula and one or more of the "&E1&", "&F1&", "&G1&" will end up looking like this; "&G1&"".  So the system is trowing in an extra " in there and that's why it won't accept the formula.  I have another laptop so I'm going to try it on that one and see if it does the same thing.  Believe me, I have tried many times and it screws it up every time.

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?
0
 
LVL 50
ID: 36997487
You cannot end the formula with G1&"  . Why would you put that at the end of the formula? Don't you understand what the & does? I've tried to explain it above, but maybe I have failed.

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

Start with just TWO cells

=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?
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36997537
I think I know what the problem is. Unfortunately, I can't use colors in posts here, so I attach an image instead. image
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
0
 

Author Comment

by:camtz
ID: 37001042
Finally, I got it working.  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 and I THANK YOU SO MUCH.  Once I complete it, I'm going to copy down and it should work.  My only concern is what if it takes more than one Row to put all the information in there.  I can easily see that happening.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37002414
I posted this as part of my Better Concatenate Function article (still in editor review)

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

Open in new window

0
 

Author Closing Comment

by:camtz
ID: 37002472
Many thanks
0
 
LVL 50
ID: 37002878
Thanks for the grade.

>> 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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 37004186
camtz,

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.
0
 

Author Comment

by:camtz
ID: 37004580
jimpen:
You are correct.  I did not delete the original worksheet.  My boss just wanted some of the information formatted a certain way for his iPad.
Thanks again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

862 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question