Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy column range of "single word" cells with spaces to a single 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy column range of "single word" cells with spaces to a sing
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy column range of "single word" cells with spaces to a sing
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column of cells to a single cell? | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
transpose 3d cells to a column in single workbook | Excel Discussion (Misc queries) |