Excel Help

[Rhino172]

This is either really easy and my brain is fried so I can't work it out, or it's impossible lol

Column A is a list of numbers followed by an ";", Example

1 ;
2 ;
3 ;
4 ;
5 ;

I need all of these to be in one cell, for example in B1 I need it to read "1 ;2 ;3 ;4 ;5" etc

Issue is the list is literally Hundreds of numbers, otherwise I'd simply use =A1&A2&A3 etc

 

[Brand86]

Concatenate mate will probably do the job...I'll just try it to see if it works.

Ok think it's more complex then I thought, doesn't work how I thought it would for some reason.

Do you need the semi-colons? Otherwise I would:

Ctrl + F, replace all ";" with " " (blank space)
Copy all the numbers and past special and transpose - this will give you all the numbers on a row rather than column
Go to the furthest cell on the right and use the concatenate formula

 

[Rhino172]

tried that, it's no different to using =A1&A2&A3, the problem is the shear volume of cells, it's gonna take me an age to enter the formula.

EDIT: yeah, unfortunately I do need the " ;" between each number...

 

[The Boosh!]

tried that, it's no different to using =A1&A2&A3, the problem is the shear volume of cells, it's gonna take me an age to enter the formula.

without seeing the exact sutiation/data set i wouldn't be able to tell you the best way of doing it, but based on what you have posted aboved you could do it very crudely by doing:

1) Select the set of cells - Lets say a1: a550
2) copy cells
3) new work sheet
4) right click cell a1
5) paste special > values > transpose
6) select cells a1: b550
7) copy
9) open notepad
10) paste
11) copy whats in note pad
12) paste into excel cell a1/where ever you want it.

there will be a more efficient way of doing it. In fact I can guarentee there is a function that will do exactly that, but the above also does it.

 

[Rhino172]

Perfectly obvious and works a treat...my brain is definately fried. Cheers

 

[Brand86]

Luke we are almost geniuses, only minus the skills or intellect....

 

[The Boosh!]

also i can see the above getting messy if you've got data next to the numbers... google do loop using vba if you're handy with coding.

 

[The Boosh!]

Luke we are almost geniuses, only minus the skills or intellect....

knows