using formula - extract text separated by 2 spaces
 

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

Looking fwd for a solution to my problem - Thxs a lot

using formula - extract text separated by 2 spaces
 

On Sunday, June 19, 2016 at 6:43:33 AM UTC+4, ANG wrote:
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

Looking fwd for a solution to my problem - Thxs a lot

OR PLS ALSO GIVE ME A MACRO TO DO IT

using formula - extract text separated by 2 spaces
 

On Sunday, June 19, 2016 at 6:43:33 AM UTC+4, ANG wrote:
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

Looking fwd for a solution to my problem - Thxs a lot

OR PLS ALSO GIVE ME A MACRO TO DO IT

How is the source data laid out?
Where does the result go in relation to the source data?

--
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

using formula - extract text separated by 2 spaces
 

10001031 BISCUIT TUC NATURE 100G Ea BEACH SHOP -6 Transfer material between subinventories 3435516 is in cell A1
Formulas/results have to be in cell B1,C1,D1,E1,....etc

Thxs

using formula - extract text separated by 2 spaces
 

On 6/18/2016 11:21 PM, ANG wrote:
10001031 BISCUIT TUC NATURE 100G Ea BEACH SHOP -6 Transfer material between subinventories 3435516 is in cell A1
Formulas/results have to be in cell B1,C1,D1,E1,....etc
Thxs

Dim strArray() As String, strTest as string
'assign the text in cell A1 to the strTest variable
strArray = Split(strTest, " ")
'split strTest into StrArray elements using two spaces as the delimiter
'now assign the elements of the array to the cells you want.

Will that work for you?

using formula - extract text separated by 2 spaces
 

Thxs
Pls give me the codes if all my data are in column D as am beginner in vba

using formula - extract text separated by 2 spaces
 

10001031 BISCUIT TUC NATURE 100G Ea BEACH SHOP -6 Transfer
material between subinventories 3435516 is in cell A1
Formulas/results have to be in cell B1,C1,D1,E1,....etc

Thxs

try...

Sub ParseValues()
Dim vData
vData = Split([A1].Value, " ")
With Range("B1").Resize(1, UBound(vData) + 1)
.Value = vData: .Columns.AutoFit
End With
End Sub

--
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

using formula - extract text separated by 2 spaces
 

Thxs
Pls give me the codes if all my data are in column D as am beginner
in vba

You told me your data was in A1! WTF are you playing at?? If you want
help give us valid info to work with!!!
Geez...

--
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

using formula - extract text separated by 2 spaces
 

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.
--
Windows10
Office 2016

using formula - extract text separated by 2 spaces
 

Hi,

Am Sun, 19 Jun 2016 12:01:51 +0200 schrieb Claus Busch:

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

and copy down


Regards
Claus B.
--
Windows10
Office 2016

using formula - extract text separated by 2 spaces
 

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

using formula - extract text separated by 2 spaces
 

Hi All,

Am Sun, 19 Jun 2016 06:06:56 -0400 schrieb GS:

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

sorry, my bad
Try in E1:
=TRIM(MID(SUBSTITUTE($D1," ",REPT(" ",99)),COLUMN(A1)*99-98,99))
and copy to the right and down


Regards
Claus B.
--
Windows10
Office 2016

using formula - extract text separated by 2 spaces
 

Hi All,

Am Sun, 19 Jun 2016 06:06:56 -0400 schrieb GS:

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

sorry, my bad
Try in E1:
=TRIM(MID(SUBSTITUTE($D1," ",REPT(" ",99)),COLUMN(A1)*99-98,99))
and copy to the right and down


Regards
Claus B.

I was refering to your code...

--
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

using formula - extract text separated by 2 spaces
 

Ok got other same data in a2,a3,... Ie in column A

using formula - extract text separated by 2 spaces
 

Hi Garry,

Am Sun, 19 Jun 2016 06:20:49 -0400 schrieb GS:

I was refering to your code...

I know. I didn't read the OP's question carefully enough. So my formula
and also the code do the output into one column. I didn't improve my
code because your code works fine.


Regards
Claus B.
--
Windows10
Office 2016

using formula - extract text separated by 2 spaces
 

Ok got other same data in a2,a3,... Ie in column A

What row does the data start in?
Are there headers?
Is the data consistent in structure?


Sub ParseValues2()
Dim vData, vTmp, lLastRow&, n&

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
vData = Range("A1:A" & lLastRow)
For n = 1 To UBound(vData)
vTmp = Split(vData(n, 1), " ")
Range("B" & n).Resize(1, UBound(vTmp) + 1) = vTmp
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Above code assumes data starts in row1

--
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

using formula - extract text separated by 2 spaces
 

On Sunday, June 19, 2016 at 2:15:27 PM UTC+4, Claus Busch wrote:
Hi All,

Am Sun, 19 Jun 2016 06:06:56 -0400 schrieb GS:

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

sorry, my bad
Try in E1:
=TRIM(MID(SUBSTITUTE($D1," ",REPT(" ",99)),COLUMN(A1)*99-98,99))
and copy to the right and down


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus
THe formula working great
Can you simplify it by ignoring cell A1 and assuming the data is in D1,d2,d3 - get the same result

using formula - extract text separated by 2 spaces
 

Hi,

Am Sun, 19 Jun 2016 06:03:38 -0700 (PDT) schrieb ANG:

Can you simplify it by ignoring cell A1 and assuming the data is in D1,d2,d3 - get the same result

Column(A1) is a counter for the MID function and must remain in the
formula.


Regards
Claus B.
--
Windows10
Office 2016

using formula - extract text separated by 2 spaces
 

Thxs a lot I've managed to understand it

using formula - extract text separated by 2 spaces
 

On Sunday, June 19, 2016 at 2:02:41 PM UTC+4, Claus Busch wrote:
Hi,

Am Sun, 19 Jun 2016 12:01:51 +0200 schrieb Claus Busch:

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

and copy down


Regards
Claus B.
--
Windows10
Office 2016

Dear Claus
Sorry to bother again - but can you give me the vba code which would get the same result as the above formula if all data are in column D

thxs

using formula - extract text separated by 2 spaces
 

i,

'Claus's solution

Sub Macro1()
lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("E1:K" & lLastRow).Formula = "=TRIM(MID(SUBSTITUTE($D1,"" "",REPT(""
"",99)),COLUMN(A1)*99-98,99))"
End Sub

'Garry's solution

Sub ParseValues2()
Dim vData, vTmp, lLastRow&, n&
lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
vData = Range("D1:D" & lLastRow)
For n = 1 To UBound(vData)
vTmp = Split(vData(n, 1), " ")
Range("E" & n).Resize(1, UBound(vTmp) + 1) = vTmp
ActiveSheet.UsedRange.Columns.AutoFit
Next
End Sub:

i tried both solutions and they work very well.
http://www.cjoint.com/c/FFtsG7PX7na

isabelle


Le 2016-06-19 Ã* 13:57, ANG a écrit :

Dear Claus
Sorry to bother again - but can you give me the vba code which would get the same result as the above formula if all data are in column D

thxs

using formula - extract text separated by 2 spaces
 

On Sunday, June 19, 2016 at 10:35:44 PM UTC+4, isabelle wrote:
i,

'Claus's solution

Sub Macro1()
lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("E1:K" & lLastRow).Formula = "=TRIM(MID(SUBSTITUTE($D1,"" "",REPT(""
"",99)),COLUMN(A1)*99-98,99))"
End Sub

'Garry's solution

Sub ParseValues2()
Dim vData, vTmp, lLastRow&, n&
lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
vData = Range("D1:D" & lLastRow)
For n = 1 To UBound(vData)
vTmp = Split(vData(n, 1), " ")
Range("E" & n).Resize(1, UBound(vTmp) + 1) = vTmp
ActiveSheet.UsedRange.Columns.AutoFit
Next
End Sub:

i tried both solutions and they work very well.
http://www.cjoint.com/c/FFtsG7PX7na

isabelle


Le 2016-06-19 Ã* 13:57, ANG a écrit :

Dear Claus
Sorry to bother again - but can you give me the vba code which would get the same result as the above formula if all data are in column D

thxs


Thxs Isa (:-)