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.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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.
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
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:


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

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

Yes, only 1 second :)
Saqib Husain, SyedEngineerCommented:
Did you scratch you eyebrow before posting?
I sneezed ;)
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

Open in new window

                                Cell B2

Open in new window

and just formula dropdown.

See Attached file.

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.

All Courses

From novice to tech pro — start learning today.