Excel Problem, couldn't be bothered finding other thread!!!!

[brokencrossbar1]

Lads and lassies, I am trying to get information from a multi column document into a single column format.  Can anyone suggest how it can be done?   Merging cells does not work so don't bother suggesting that

[Treasurer]

Might be a better way but this would work..

In a new column enter the formula =a1&b1&c1&d1 ...... etc

If you want spaces use =a1&" "&b1&" "... etc

Copy the formula down

Then select the column, copy, then move to new column and "paste special" "formulas as values" and you can delete the original columns.

Hope that's what you meant.

[Muzz]

Treasurer idea will work but instead of all the & signs you can do the formula =concatenate(A1,B1).  That will not put spaces between them.  But if you want spaces you do =concatenate(A1," ",B1) etc. depending on the amount of columns you have.

[Donagh]

In the cell were you want the results to show, use a formula like:

=(A1&B1)        

given that A1 and B1 are the two columns you want to merge.

Use a fill down to populate the rest.

[brokencrossbar1]

Thanks folks, used the Lovely Treas's way and it worked a treat.  I promise now to really look after you in the Slurry Cup match

[brokencrossbar1]

One small issue is that some of the columns are fixed width, 10 numbers per cell.  For the report I am tryning to populate i need all of those 10 numbers but many of them are 0 and therefore only coming across as single digits.

Anymore help???

[Donagh]

Not exactly sure what you mean, but you should be able to format the cells (right-click on selected cells?format cells) any way you want.

[Treasurer]

Hmm  I see what you mean about losing the formats.  I can't find a way to "and" them without losing the format but there is a rather long winded way around it - if nobody has any better ideas?  Do-able if you haven't loads of columns.

Insert a column at the left of the page, put in ten zeroes as text, ie hit the apostrophe and put in your ten zeroes.  Then fill down.

Then change your formula to =right($a1&b1,10)&" "&right($a1&c1,10) etc

This will combine the ten zeroes with the value in the column, then just take the ten characters to the right so if your number has ten characters it will just take them, but if it has only 1 it will add 9 zeroes - if that's making any sense.


Copy down and paste as values as previously.

I'm sure there's a shorter way, but I'm quite pleased with myself

[Smokin Joe]

bcb, the problem is that by using the "&" method, or indeed the cocantate (sp) method you are effectively creating a text string, hence why you can't format your single column output.

[Donagh]

Looks like the solution here:

http://uk.youtube.com/watch?v=tC24GnCzC2Y