Hi,

Am Sat, 18 Jun 2016 19:43:22 -0700 (PDT) schrieb ANG:

Would be great if someone could give me a formula which would
separate text between 2 spaces e.g 10001031 BISCUIT TUC NATURE 100G
Ea BEACH SHOP -6 Transfer material between subinventories
3435516 i.e would like to have per below in diff cells: 10001031
BISCUIT TUC NATURE 100G
Ea
BEACH SHOP
-6
Transfer material between subinventories
3435516

for only one string in D1 use in E1:
=TRIM(MID(SUBSTITUTE(D$1," ",REPT(" ",99)),ROW(D1)*99-98,99))

If you have more data in D1 use Garrys procedure or

Sub SeparateText()
Dim LRow As Long, i As Long
Dim varTmp() As Variant, varOut As Variant
Dim myStr As String

LRow = Cells(Rows.Count, "D").End(xlUp).Row
ReDim varTmp(LRow - 1)
For i = 1 To LRow
varTmp(i - 1) = Cells(i, "D")
Next
myStr = Join(varTmp, " ")
varOut = Split(myStr, " ")
Range("E1").Resize(UBound(varOut) + 1) =
Application.Transpose(varOut) End Sub


Regards
Claus B.

Claus,
This creates a column of results where OP requests same row starting in
E.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus