Copy column range of "single word" cells with spaces to a single c

060214 Copy column range of "single word" cells with spaces to a single cell

Hi, is there a formula or script for copying a range of cells, that have
single word-text, to a single cell with spaces between each word? Thanks!

have: =A100&" "&A101&" " ... &A400&" "&A401&" "
but have many records / unwanted space lines.
(possible answer to excluding "." periods might be using FIND-REPLACE
afterwards.

maybe something like:
=IF(OR(A1=".",A1=".blank."),then skip,A1:A3000)&" ")

not code literate, so if can include instruction like (enter your range
here, do this/that; can / cannot use with other code.. thanks)
have code from somewhere, but do not know how to use, if someone can expand
a little, thanks!!

x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
For Each c In Sheets(1).Range(Cells(5, 1), Cells(x, 1))
symbols = symbols & "+" & c

Use that long formula:
=A100&" "&A101&" " ... &A400&" "&A401&" "
but wrap trim around it:
=trim(A100&" "&A101&" " ... &A400&" "&A401&" ")

Trim will remove any leading/trailing/duplicated internal spaces.

For lots of cells, you may want to use a userdefined function:
http://mcgimpsey.com/excel/udfs/multicat.html
(From J.E. McGimpsey)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

nastech wrote:
>
> 060214 Copy column range of "single word" cells with spaces to a single cell
>
> Hi, is there a formula or script for copying a range of cells, that have
> single word-text, to a single cell with spaces between each word? Thanks!
>
> have: =A100&" "&A101&" " ... &A400&" "&A401&" "
> but have many records / unwanted space lines.
> (possible answer to excluding "." periods might be using FIND-REPLACE
> afterwards.
>
> maybe something like:
> =IF(OR(A1=".",A1=".blank."),then skip,A1:A3000)&" ")
>
> not code literate, so if can include instruction like (enter your range
> here, do this/that; can / cannot use with other code.. thanks)
> have code from somewhere, but do not know how to use, if someone can expand
> a little, thanks!!
>
> x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
> For Each c In Sheets(1).Range(Cells(5, 1), Cells(x, 1))
> symbols = symbols & "+" & c

--

Dave Peterson

thanks, will try look at it more later, is a medium/minor thing, just having
to type dozens of symbols over-again daily. multicat / site seems to do same
with code, don't see how to operate it once you place on sheet as code, how
input/ range would work, if did not know answer to start with.. can tell
these are the right answers though.. will check it out.



"Dave Peterson" wrote:

> Use that long formula:
> =A100&" "&A101&" " ... &A400&" "&A401&" "
> but wrap trim around it:
> =trim(A100&" "&A101&" " ... &A400&" "&A401&" ")
>
> Trim will remove any leading/trailing/duplicated internal spaces.
>
> For lots of cells, you may want to use a userdefined function:
> http://mcgimpsey.com/excel/udfs/multicat.html
> (From J.E. McGimpsey)
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> nastech wrote:
> >
> > 060214 Copy column range of "single word" cells with spaces to a single cell
> >
> > Hi, is there a formula or script for copying a range of cells, that have
> > single word-text, to a single cell with spaces between each word? Thanks!
> >
> > have: =A100&" "&A101&" " ... &A400&" "&A401&" "
> > but have many records / unwanted space lines.
> > (possible answer to excluding "." periods might be using FIND-REPLACE
> > afterwards.
> >
> > maybe something like:
> > =IF(OR(A1=".",A1=".blank."),then skip,A1:A3000)&" ")
> >
> > not code literate, so if can include instruction like (enter your range
> > here, do this/that; can / cannot use with other code.. thanks)
> > have code from somewhere, but do not know how to use, if someone can expand
> > a little, thanks!!
> >
> > x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
> > For Each c In Sheets(1).Range(Cells(5, 1), Cells(x, 1))
> > symbols = symbols & "+" & c
>
> --
>
> Dave Peterson
>

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

nastech wrote:
>
> thanks, will try look at it more later, is a medium/minor thing, just having
> to type dozens of symbols over-again daily. multicat / site seems to do same
> with code, don't see how to operate it once you place on sheet as code, how
> input/ range would work, if did not know answer to start with.. can tell
> these are the right answers though.. will check it out.
>
> "Dave Peterson" wrote:
>
> > Use that long formula:
> > =A100&" "&A101&" " ... &A400&" "&A401&" "
> > but wrap trim around it:
> > =trim(A100&" "&A101&" " ... &A400&" "&A401&" ")
> >
> > Trim will remove any leading/trailing/duplicated internal spaces.
> >
> > For lots of cells, you may want to use a userdefined function:
> > http://mcgimpsey.com/excel/udfs/multicat.html
> > (From J.E. McGimpsey)
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > nastech wrote:
> > >
> > > 060214 Copy column range of "single word" cells with spaces to a single cell
> > >
> > > Hi, is there a formula or script for copying a range of cells, that have
> > > single word-text, to a single cell with spaces between each word? Thanks!
> > >
> > > have: =A100&" "&A101&" " ... &A400&" "&A401&" "
> > > but have many records / unwanted space lines.
> > > (possible answer to excluding "." periods might be using FIND-REPLACE
> > > afterwards.
> > >
> > > maybe something like:
> > > =IF(OR(A1=".",A1=".blank."),then skip,A1:A3000)&" ")
> > >
> > > not code literate, so if can include instruction like (enter your range
> > > here, do this/that; can / cannot use with other code.. thanks)
> > > have code from somewhere, but do not know how to use, if someone can expand
> > > a little, thanks!!
> > >
> > > x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
> > > For Each c In Sheets(1).Range(Cells(5, 1), Cells(x, 1))
> > > symbols = symbols & "+" & c
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson