I need to combine about 3000 lines of numbers into a single cell, separated
by a ";". Is there a quick way to do this without having to type each of the
3000 cells into the formula? Thanks for any help.

It is highly unlikely thet the data in 3000 cells will fitin one, but here
is the general principle

Sub Concat()Dim tgtrng As Range
Dim cell As Range
Dim tmp As String

For Each cell In Selection
If Not cell.Value = "" Then tmp = tmp & cell.Value & ","
Next cell
If tmp < "" Then range("A1").Value = Left(tmp, Len(tmp) - 1)

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Cassie" wrote in message
...
I need to combine about 3000 lines of numbers into a single cell, separated
by a ";". Is there a quick way to do this without having to type each of
the
3000 cells into the formula? Thanks for any help.

It isn't a formula, but this could work:
Highlight the numbers you want.
Copy using CTRL+C
Create a new word document
Paste Special using the edit menu and paste it as plain text.
Press CTRL+H and in the find field put ^w and in the replace field put ;
Still on the replace menu, put ^p in the find field and ; in the replace
field.
Visually inspect the data to make sure there are no ;;; (There were in my
test data because I had blank rows). If there are, find ;;; and replace with
;.
Now copy that text, and paste it back into Excel.

Brilliant. I have used the copy & paste special feature for shorter lists,
manually typing the ;+del+end and repeating until all the numbers were
linked. But I didn't know about using the replace feature. I actually have
to copy this list of numbers into another software program, so whether the
numbers are merged in Excel or Word doesn't really matter. Thank you for
saving me from carpal tunnel!

"~L" wrote:

It isn't a formula, but this could work:
Highlight the numbers you want.
Copy using CTRL+C
Create a new word document
Paste Special using the edit menu and paste it as plain text.
Press CTRL+H and in the find field put ^w and in the replace field put ;
Still on the replace menu, put ^p in the find field and ; in the replace
field.
Visually inspect the data to make sure there are no ;;; (There were in my
test data because I had blank rows). If there are, find ;;; and replace with
;.
Now copy that text, and paste it back into Excel.

When I'm braver and have more time to practive this (when do we ever NOT have
deadlines?), I will definitely try this. I actually have to copy & paste the
string of numbers into another software application, so whether it stays in
Excel or gets converted to a Word or txt file doesn't matter. I've never had
to combine so many numbers at once, so I was at a loss. Thanks for the info.

"Bob Phillips" wrote:

It is highly unlikely thet the data in 3000 cells will fitin one, but here
is the general principle

Sub Concat()Dim tgtrng As Range
Dim cell As Range
Dim tmp As String

For Each cell In Selection
If Not cell.Value = "" Then tmp = tmp & cell.Value & ","
Next cell
If tmp < "" Then range("A1").Value = Left(tmp, Len(tmp) - 1)

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Cassie" wrote in message
...
I need to combine about 3000 lines of numbers into a single cell, separated
by a ";". Is there a quick way to do this without having to type each of
the
3000 cells into the formula? Thanks for any help.

Well, there is a way to do this with formulas.

Assume your data are in A1:A3000

In B2 enter =CONCATENATE(A1,";"A2)
In B3 enter =CONCATENATE(B2,";",A3)

Fill down to B3000.

Copy/paste special/values to strip the formula.

Then you have your three thousand text items separated by a semicolon.

Dave
--
Brevity is the soul of wit.


"Cassie" wrote:

Brilliant. I have used the copy & paste special feature for shorter lists,
manually typing the ;+del+end and repeating until all the numbers were
linked. But I didn't know about using the replace feature. I actually have
to copy this list of numbers into another software program, so whether the
numbers are merged in Excel or Word doesn't really matter. Thank you for
saving me from carpal tunnel!

"~L" wrote:

It isn't a formula, but this could work:
Highlight the numbers you want.
Copy using CTRL+C
Create a new word document
Paste Special using the edit menu and paste it as plain text.
Press CTRL+H and in the find field put ^w and in the replace field put ;
Still on the replace menu, put ^p in the find field and ; in the replace
field.
Visually inspect the data to make sure there are no ;;; (There were in my
test data because I had blank rows). If there are, find ;;; and replace with
;.
Now copy that text, and paste it back into Excel.

I knew three had to be a way to do this. I just couldn't put my fingers on
it. I will definitely remember this one for future use. And all 3000
numbers and ;'s did fit into the last "paste special;" cell. Thanks so much
for your help.

"Dave F" wrote:

Well, there is a way to do this with formulas.

Assume your data are in A1:A3000

In B2 enter =CONCATENATE(A1,";"A2)
In B3 enter =CONCATENATE(B2,";",A3)

Fill down to B3000.

Copy/paste special/values to strip the formula.

Then you have your three thousand text items separated by a semicolon.

Dave
--
Brevity is the soul of wit.


"Cassie" wrote:

Brilliant. I have used the copy & paste special feature for shorter lists,
manually typing the ;+del+end and repeating until all the numbers were
linked. But I didn't know about using the replace feature. I actually have
to copy this list of numbers into another software program, so whether the
numbers are merged in Excel or Word doesn't really matter. Thank you for
saving me from carpal tunnel!

"~L" wrote:

It isn't a formula, but this could work:
Highlight the numbers you want.
Copy using CTRL+C
Create a new word document
Paste Special using the edit menu and paste it as plain text.
Press CTRL+H and in the find field put ^w and in the replace field put ;
Still on the replace menu, put ^p in the find field and ; in the replace
field.
Visually inspect the data to make sure there are no ;;; (There were in my
test data because I had blank rows). If there are, find ;;; and replace with
;.
Now copy that text, and paste it back into Excel.

Hi Bob,
Is it possible to concatenate a 825 cell cell-range into 1 cell by using a
formula and not w/ VBA? My user group does not know VBA.

thanks! Jane

"Bob Phillips" wrote:

It is highly unlikely thet the data in 3000 cells will fitin one, but here
is the general principle

Sub Concat()Dim tgtrng As Range
Dim cell As Range
Dim tmp As String

For Each cell In Selection
If Not cell.Value = "" Then tmp = tmp & cell.Value & ","
Next cell
If tmp < "" Then range("A1").Value = Left(tmp, Len(tmp) - 1)

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Cassie" wrote in message
...
I need to combine about 3000 lines of numbers into a single cell, separated
by a ";". Is there a quick way to do this without having to type each of
the
3000 cells into the formula? Thanks for any help.

"Jane" wrote...
Is it possible to concatenate a 825 cell cell-range into 1 cell by using a
formula and not w/ VBA? My user group does not know VBA.
....

No. Built-in operators and functions can't cram 825 cell addresses with &
operators between them into one formula. The ONLY way to do this in a SINGLE
cell involves using VBA. The alternatives involve using multiple cells in a
divide & conquer approach, e.g.,

B1:
=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A1 5&A16&A17&A18&A19&A20
&A21&A22&A23&A24&A25&A26&A27&A28&A29&A30&A31&A32&A 33&A34&A35&A36&A37&A38
&A39&A40&A41&A42&A43&A44&A45&A46&A47&A48&A49&A50&A 51&A52&A53&A54&A55&A56
&A57&A58&A59&A60&A61&A62&A63&A64&A65&A66&A67&A68&A 69&A70&A71&A72&A73&A74
&A75&A76&A77&A78&A79&A80&A81&A82&A83&A84&A85&A86&A 87&A88&A89&A90&A91&A92
&A93&A94&A95&A96&A97&A98&A99&A100&A101&A102&A103&A 104&A105&A106&A107&A108
&A109&A110&A111&A112&A113&A114&A115&A116&A117&A118 &A119&A120&A121&A122&A123
&A124&A125&A126&A127&A128&A129&A130&A131&A132&A133 &A134&A135&A136&A137&A138
&A139&A140&A141&A142&A143&A144&A145&A146&A147&A148 &A149&A150&A151&A152&A153
&A154&A155&A156&A157&A158&A159&A160&A161&A162&A163 &A164&A165&A166&A167&A168
&A169&A170&A171&A172&A173&A174&A175&A176&A177&A178 &A179&A180&A181&A182&A183
&A184&A185&A186&A187&A188&A189&A190&A191&A192&A193 &A194&A195&A196&A197&A198
&A199&A200&A201&A202&A203&A204&A205&A206&A207

B2:
=A208&A209&A210&A211&A212&A213&A214&A215&A216&A217 &A218&A219&A220&A221&A222
&A223&A224&A225&A226&A227&A228&A229&A230&A231&A232 &A233&A234&A235&A236&A237
&A238&A239&A240&A241&A242&A243&A244&A245&A246&A247 &A248&A249&A250&A251&A252
&A253&A254&A255&A256&A257&A258&A259&A260&A261&A262 &A263&A264&A265&A266&A267
&A268&A269&A270&A271&A272&A273&A274&A275&A276&A277 &A278&A279&A280&A281&A282
&A283&A284&A285&A286&A287&A288&A289&A290&A291&A292 &A293&A294&A295&A296&A297
&A298&A299&A300&A301&A302&A303&A304&A305&A306&A307 &A308&A309&A310&A311&A312
&A313&A314&A315&A316&A317&A318&A319&A320&A321&A322 &A323&A324&A325&A326&A327
&A328&A329&A330&A331&A332&A333&A334&A335&A336&A337 &A338&A339&A340&A341&A342
&A343&A344&A345&A346&A347&A348&A349&A350&A351&A352 &A353&A354&A355&A356&A357
&A358&A359&A360&A361&A362&A363&A364&A365&A366&A367 &A368&A369&A370&A371&A372
&A373&A374&A375&A376&A377&A378&A379&A380&A381&A382 &A383&A384&A385&A386&A387
&A388&A389&A390&A391&A392&A393&A394&A395&A396&A397 &A398&A399&A400&A401&A402
&A403&A404&A405&A406&A407&A408&A409&A410

B3:
=A411&A412&A413&A414&A415&A416&A417&A418&A419&A420 &A421&A422&A423&A424&A425
&A426&A427&A428&A429&A430&A431&A432&A433&A434&A435 &A436&A437&A438&A439&A440
&A441&A442&A443&A444&A445&A446&A447&A448&A449&A450 &A451&A452&A453&A454&A455
&A456&A457&A458&A459&A460&A461&A462&A463&A464&A465 &A466&A467&A468&A469&A470
&A471&A472&A473&A474&A475&A476&A477&A478&A479&A480 &A481&A482&A483&A484&A485
&A486&A487&A488&A489&A490&A491&A492&A493&A494&A495 &A496&A497&A498&A499&A500
&A501&A502&A503&A504&A505&A506&A507&A508&A509&A510 &A511&A512&A513&A514&A515
&A516&A517&A518&A519&A520&A521&A522&A523&A524&A525 &A526&A527&A528&A529&A530
&A531&A532&A533&A534&A535&A536&A537&A538&A539&A540 &A541&A542&A543&A544&A545
&A546&A547&A548&A549&A550&A551&A552&A553&A554&A555 &A556&A557&A558&A559&A560
&A561&A562&A563&A564&A565&A566&A567&A568&A569&A570 &A571&A572&A573&A574&A575
&A576&A577&A578&A579&A580&A581&A582&A583&A584&A585 &A586&A587&A588&A589&A590
&A591&A592&A593&A594&A595&A596&A597&A598&A599&A600 &A601&A602&A603&A604&A605
&A606&A607&A608&A609&A610&A611&A612

B4:
=A613&A614&A615&A616&A617&A618&A619&A620&A621&A622 &A623&A624&A625&A626&A627
&A628&A629&A630&A631&A632&A633&A634&A635&A636&A637 &A638&A639&A640&A641&A642
&A643&A644&A645&A646&A647&A648&A649&A650&A651&A652 &A653&A654&A655&A656&A657
&A658&A659&A660&A661&A662&A663&A664&A665&A666&A667 &A668&A669&A670&A671&A672
&A673&A674&A675&A676&A677&A678&A679&A680&A681&A682 &A683&A684&A685&A686&A687
&A688&A689&A690&A691&A692&A693&A694&A695&A696&A697 &A698&A699&A700&A701&A702
&A703&A704&A705&A706&A707&A708&A709&A710&A711&A712 &A713&A714&A715&A716&A717
&A718&A719&A720&A721&A722&A723&A724&A725&A726&A727 &A728&A729&A730&A731&A732
&A733&A734&A735&A736&A737&A738&A739&A740&A741&A742 &A743&A744&A745&A746&A747
&A748&A749&A750&A751&A752&A753&A754&A755&A756&A757 &A758&A759&A760&A761&A762
&A763&A764&A765&A766&A767&A768&A769&A770&A771&A772 &A773&A774&A775&A776&A777
&A778&A779&A780&A781&A782&A783&A784&A785&A786&A787 &A788&A789&A790&A791&A792
&A793&A794&A795&A796&A797&A798&A799&A800&A801&A802 &A803&A804&A805&A806&A807
&A808&A809&A810&A811&A812&A813&A814

B5:
=B1&B2&B3&B4&A815&A816&A817&A818&A819&A820&A821&A8 22&A823&A824&A825

This is the only way to do this sort of thing without VBA. Let your user
group see this as a good reason to learn VBA.