Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default 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?


  #6   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default using formula - extract text separated by 2 spaces

Ok got other same data in a2,a3,... Ie in column A
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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


  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #17   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #19   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default using formula - extract text separated by 2 spaces

Thxs a lot I've managed to understand it
  #20   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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


  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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

  #22   Report Post  
Posted to microsoft.public.excel.programming
ANG ANG is offline
external usenet poster
 
Posts: 12
Default 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 (:-)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
extract text between two spaces anthony Excel Discussion (Misc queries) 5 April 21st 23 05:24 PM
Extract all text without spaces and special characters Alfredo_CPA Excel Worksheet Functions 4 September 18th 08 11:39 PM
how to taking a .txt email list that are separated with spaces and sort them in to excel cells [email protected] Excel Worksheet Functions 2 August 3rd 07 12:28 AM
taking a .txt email list that are separated with spaces and sort them in to excel cells [email protected] Excel Programming 1 August 2nd 07 10:11 PM
How can I extract the first & last name separated by a comma MSA Excel Discussion (Misc queries) 5 September 18th 06 03:28 PM


All times are GMT +1. The time now is 10:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"