Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
break at space
Hello, I have thousands of nouns separated by a space (abandonments
abbreviating abbreviation abbreviators ...) and I would like to put a formula so that at each space the remaining is copied to the next cell (killing the needless space). Can it be done, can you hint me to how to do this? thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
break at space
I forgot to mention that I have all the words in one cell.
Thanks ____ "Learner" wrote in message ... Hello, I have thousands of nouns separated by a space (abandonments abbreviating abbreviation abbreviators ...) and I would like to put a formula so that at each space the remaining is copied to the next cell (killing the needless space). Can it be done, can you hint me to how to do this? thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
break at space
Hmmmm,
Perhaps under Data Text to Columns Delimiter Space OK Finish. Try it on a test set of data first!!! HTH Regrds, Howard "Learner" wrote in message ... Hello, I have thousands of nouns separated by a space (abandonments abbreviating abbreviation abbreviators ...) and I would like to put a formula so that at each space the remaining is copied to the next cell (killing the needless space). Can it be done, can you hint me to how to do this? thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
break at space
I think this macro does what I think you are saying you want to do (just
change the Worksheet name and Range reference for the cell containing the words in the With statement to match your actual set up)... Sub SplitDownAtSpace() Dim Words() As String With Worksheets("Sheet4").Range("A1") Words = Split(.Value) .Resize(UBound(Words) + 1).Value = WorksheetFunction.Transpose(Words) End With End Sub -- Rick (MVP - Excel) "Learner" wrote in message ... Hello, I have thousands of nouns separated by a space (abandonments abbreviating abbreviation abbreviators ...) and I would like to put a formula so that at each space the remaining is copied to the next cell (killing the needless space). Can it be done, can you hint me to how to do this? thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
break at space
Thank you Howard and Rick, Rick's code produces exactly what I was looking
for. "Rick Rothstein" wrote in message ... I think this macro does what I think you are saying you want to do (just change the Worksheet name and Range reference for the cell containing the words in the With statement to match your actual set up)... Sub SplitDownAtSpace() Dim Words() As String With Worksheets("Sheet4").Range("A1") Words = Split(.Value) .Resize(UBound(Words) + 1).Value = WorksheetFunction.Transpose(Words) End With End Sub -- Rick (MVP - Excel) "Learner" wrote in message ... Hello, I have thousands of nouns separated by a space (abandonments abbreviating abbreviation abbreviators ...) and I would like to put a formula so that at each space the remaining is copied to the next cell (killing the needless space). Can it be done, can you hint me to how to do this? thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard for space text space pattern | Excel Programming | |||
XL2003 VBA: Debuging...can not break or run in break/step mode. | Excel Programming | |||
Discovering No-BREAK Space in cell Data | Excel Discussion (Misc queries) | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
Paper Space / Model Space ? | Excel Discussion (Misc queries) |