On 3 nov, 19:20, "Rick Rothstein"
wrote:
Select the cell or cells you want to process and run the following macro....

Sub SplitOnTwoSpaces()
* Dim C As Range, Parts() As String
* For Each C In Selection
* * Parts = Split(Replace(C.Value, " ", Chr$(1), , 1), " ", 3)
* * Parts(0) = Replace(Parts(0), Chr$(1), " ")
* * C.Offset(0, 1).Resize(1, 3).Value = Parts
* Next
End Sub

--
Rick (MVP - Excel)

"ppeer" wrote in message

...



Hi Expert,

I would like to split the following cell (by a routine not a worksheet
formula)
on the second and third space:

input CellA:
1044 GH Place Other information

output:
CellB * * * * *CellC * * * * * CellD
1044 GH * * Place * * * * * Other Information

Please, input very welcome.

best regards,
Peter- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thank you Rick, Peter, Ron. After running the macro's and seeing the
results, I discovered some returning words and short phrases, which I
first have to filter-out/move a column to the right,
before running your macro. Some input is not like Place but like Place
Area Two, so to keep that together in one cell after splitting, I
first move the Other Informartion (which is "always the same start
text") and then do a split on the second space. The start of the cell
is always 6 digits plus a space between the first four and last two
(like 1044 GH) so that will be kept intact after the split.
The first word of Other Information (Other) is always the same. If you
have any suggestions for search, select and move this content (eg
Other Information, Other Search, Other Fab) out of the cell, 3 columns
to the right, I would be happy to know.

Thanks for the help