• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

Excel Cell Format

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
camtz
Asked:
camtz
  • 12
  • 10
  • +1
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
Sorry, I missed that.  I will try again. Thanks
0
 
camtzAuthor Commented:
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
 
camtzAuthor Commented:
Please let me know.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
I downloaded the book that you created and this is the way it looks on my computer.  Is this correct?
QKees-Screens.jpg
0
 
camtzAuthor Commented:
Screenshot 3
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
camtzAuthor Commented:
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
 
aikimarkCommented:
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
 
camtzAuthor Commented:
Many thanks
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Jim P.Commented:
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
 
camtzAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now