Excel 2010 - Adding Punctuation Around Values in Rows

Good Day Experts!

I am a novice  at Excel 2010 and am not sure if what I wish to do is possible.

I have 6541 rows with values only in column A.  The values are 4 long and have up to 3 preceeding 0's.  I would like to put ' in front and in back of the value('0002').  Then I would like to put all the values together in a string seperated by a comma('0002','0003','0004' etc).

Bascially I am going to use these values in a SQL query.

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
If your data starts in A2 then enter these formulas

B2:      =TEXT(A2,"'0000',")

C2:      =B2&C1

and copy them both all the way down.
0
 
NBVCConnect With a Mentor Commented:
Perhaps with a UDF....

press ALT+F11 to go to VB editor, then Insert|Module.

Paste this in the Editor:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Open in new window


Then in the sheet, use formula in an empty cell:

="'"&aconcat(A1:A6541,"',")&"'"

adjust range to suit.
0
 
Saqib Husain, SyedEngineerCommented:
That was close!!!
0
Get expert help—faster!

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

 
NBVCCommented:
Yes, only 1 second :)
0
 
Saqib Husain, SyedEngineerCommented:
Did you scratch you eyebrow before posting?
0
 
NBVCCommented:
I sneezed ;)
0
 
Naresh PatelTraderCommented:
this is ony suggestion i guess he needs this way assuming data start in column A from Cell A1 then formual in Cell B1
="'"&A1&"',"

Open in new window


                                Cell B2
=B1&"'"&A2&"',"

Open in new window

and just formula dropdown.


See Attached file.


Thanks
Join.xlsx
0
 
Jimbo99999Author Commented:
Thanks everyone.  The nedd/requirement was retracted from my assignment.  However, I have wondered how to do this type of "stuff" in the past.  So I will definitely put it in my knowledge base.

jimbo99999
0
All Courses

From novice to tech pro — start learning today.