Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
using formula - extract text separated by 2 spaces
Ok got other same data in a2,a3,... Ie in column A
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
using formula - extract text separated by 2 spaces
Thxs a lot I've managed to understand it
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract text between two spaces | Excel Discussion (Misc queries) | |||
Extract all text without spaces and special characters | Excel Worksheet Functions | |||
how to taking a .txt email list that are separated with spaces and sort them in to excel cells | Excel Worksheet Functions | |||
taking a .txt email list that are separated with spaces and sort them in to excel cells | Excel Programming | |||
How can I extract the first & last name separated by a comma | Excel Discussion (Misc queries) |