I need to add a comma after everything in a colum... how?

[higman.schmidt]

how do i do this...

i have text in a series of cells in a colum... i want to add a comma after
the text in each cell... the text is different in each cell.... how do i do
this????

[Anne Troy]

You could insert a column to the right and do something like =A1&","
************
Anne Troy
www.OfficeArticles.com

"higman.schmidt" <[email protected]> wrote in message
news:[email protected]...
> how do i do this...
>
> i have text in a series of cells in a colum... i want to add a comma after
> the text in each cell... the text is different in each cell.... how do i
> do
> this????

[higman.schmidt]

OK ive figured it out.... after searching for an hour or two i found that you
can just use the
=concatenate(k1," ,",c3," ",c4)
where k1 is the physical address
c3 is the city
c4 is the state

this outputs
physicaladdress, city state

"Anne Troy" wrote:

> You could insert a column to the right and do something like =A1&","
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "higman.schmidt" <[email protected]> wrote in message
> news:[email protected]...
> > how do i do this...
> >
> > i have text in a series of cells in a colum... i want to add a comma after
> > the text in each cell... the text is different in each cell.... how do i
> > do
> > this????
>
>
>

[Anne Troy]

Sure. If you use =k1&", "&c3&","&c4, then you don't even need the
concatenate part.
************
Anne Troy
www.OfficeArticles.com

"higman.schmidt" <[email protected]> wrote in message
news:[email protected]...
> OK ive figured it out.... after searching for an hour or two i found that
> you
> can just use the
> =concatenate(k1," ,",c3," ",c4)
> where k1 is the physical address
> c3 is the city
> c4 is the state
>
> this outputs
> physicaladdress, city state
>
> "Anne Troy" wrote:
>
>> You could insert a column to the right and do something like =A1&","
>> ************
>> Anne Troy
>> www.OfficeArticles.com
>>
>> "higman.schmidt" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > how do i do this...
>> >
>> > i have text in a series of cells in a colum... i want to add a comma
>> > after
>> > the text in each cell... the text is different in each cell.... how do
>> > i
>> > do
>> > this????
>>
>>
>>

[Charles Wilcockson]

Higman

Assuming you want to do this manually:

A neat way would be to define a name called COMMA and give it a value =","

Then, assuming your data is in the range A1 to A100, enter the formula =A1 &
COMMA into cell B1 and then copy and paste, or drag and fill, this through
cells B2 to B100. Any formulae or mail merges etc could then be directed to
use the modified data in range B1 to B100



To do this using VBA code (Excel 2003):

Add the following procedure to a VBA module, highlight the cells you wish to
append the comma to and run the procedure. If this were going to be run on a
regular basis I suggest you assign a shortcut key to the macro to speed
things up.



Option Explicit

Sub Append_Character()

Dim rng As Range
Dim rngCell As Range
Const sCHARACTER As String = "ABC"

On Error GoTo Exit_Append_Character

Set rng = ActiveWindow.RangeSelection

' *** a) Append sCHARACTER to all selected cells

' For Each rngCell In rng.Cells
' rngCell.Value = rngCell.Value & sCHARACTER
' Next rngCell


' *** b) Only append sCHARACTER to non blank cells that do not already end
in sCHARACTER

For Each rngCell In rng.Cells
'Skip blank cells
If IsEmpty(rngCell.Value) = False Then
'Skip cells already ending in character
If Mid(rngCell.Value, Len(rngCell.Value), Len(sCHARACTER))
<> sCHARACTER Then
rngCell.Value = rngCell.Value & sCHARACTER
End If
End If
Next rngCell

Exit_Append_Character:

Set rngCell = Nothing
Set rng = Nothing

End Sub



I have suggested two possible choices for determining when to append the
character. There are no doubt countless others but without knowing the
reason for appending the character this is the best I can do in the
circumstances.

I hope this helps.

Kind regards

Charles Wilcockson



"higman.schmidt" <[email protected]> wrote in message
news:[email protected]...
> how do i do this...
>
> i have text in a series of cells in a colum... i want to add a comma after
> the text in each cell... the text is different in each cell.... how do i
> do
> this????