concatenation issue with over 1800 IDs

gmccray

New Member
Joined
Jan 9, 2009
Messages
16
I need a quick way to concatenate over 1800 IDs like cell A1:mccrayg, A2:gmcay, A3:marshal6 ..... to se this u properly to be used in a sql query it would take forever using =Concatenate("'",A1,"'",",","'"A2, .....etc

can anyone assist me?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,253
Office Version
365
Platform
Windows
Do you want to concatenate all values in col A?
Also do you have Office 365?
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
You might make it with this:
Code:
="A"&ROW(A1)&":"&A1
=B1&", "&"A"&ROW(A2)&":"&A2
Put the first one in B1, the second in b2, then copy B2 down.
This will concatenate iteratively and you just select the last cell for the full concatenation.
BE CAREFUL: the limit for a cell length is 32K+ bytes, I tried it with values averaging 11 bytes in column A and it got to over 1800 rows before it hit the limit.
 
Last edited:

Forum statistics

Threads
1,077,855
Messages
5,336,782
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top