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

I forgot to mention that I have all the words in one cell.
Thanks
____
"Learner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Hmmmm,

Perhaps under Data > Text to Columns > Delimiter > Space > OK > Finish.

Try it on a test set of data first!!!

HTH
Regrds,
Howard


"Learner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Thank you Howard and Rick, Rick's code produces exactly what I was looking
for.


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>