Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
On Tue, 3 Nov 2009 08:34:39 -0800 (PST), ppeer wrote:
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 Here is a basic way of doing something like this: To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), first select the range to process. Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. You will have to vary the method of settting rg depending on your actual data setup and other requirements. ============================================ Option Explicit Sub Split2nd3rdSpace() Dim c As Range, rg As Range Dim s() As String Dim i As Long Set rg = Selection For Each c In rg With c s = Split(WorksheetFunction.Trim(.Value), " ") .Offset(0, 1).Value = s(0) & " " & s(1) .Offset(0, 2).Value = s(2) For i = 0 To 2 s(i) = "" Next i .Offset(0, 3) = Trim(Join(s, " ")) End With Next c End Sub ============================== --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
Another one just for luck (also caters for only 1 or 2 spaces in the
string) - Sub Split2nd3rdSpaceB() Dim pos1 As Long, pos2 As Long Dim s As String Dim rng As Range, cel As Range Set rng = Selection.Columns(1).Cells For Each cel In rng With cel s = .Value If Len(s) Then pos2 = InStr(1, s, " ") pos1 = InStr(pos2 + 1, s, " ") If pos1 Then pos2 = InStr(pos1 + 1, s, " ") .Offset(, 1) = Left$(s, pos1 - 1) If pos2 Then .Offset(, 2) = Mid$(s, pos1 + 1, pos2 - pos1 - 1) .Offset(, 3) = Mid$(s, pos2 + 1, Len(s) - pos2) Else .Offset(, 2) = Mid$(s, pos1 + 1, Len(s) - pos1) End If Else .Offset(, 1) = s End If End If End With Next End Sub Regards, Peter T "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
Okay, that is a different set of specifications, so the code to handle it
will be different. Give this macros a try... Sub SplitOnTwoSpaces() Dim C As Range, Other As Long For Each C In Selection If C.Value Like "???? ?? *[Oo]ther*" Then C.Offset(0, 1).Value = Left(C.Value, 7) Other = InStr(1, C.Value, "other", vbTextCompare) C.Offset(0, 2).Value = Mid(C.Value, 9, Other - 10) C.Offset(0, 3).Value = Mid(C.Value, Other) End If Next End Sub -- Rick (MVP - Excel) "ppeer" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
On Tue, 3 Nov 2009 12:32:17 -0800 (PST), ppeer wrote:
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 Well, a different specification. I interpreted your specifications as follows: A1: original string B1: First two words of the string C1: Third word of the string up to but not including the word "Other" D1: "Other" and everything following it Given that, it was easy to build a Regular Expression to those rules, and implement it in VBA code. (And if your specifications are different, it would be pretty straightforward to adjust the regex): ========================================== Option Explicit Sub ParseData() Dim c As Range, rg As Range Dim re As Object, mc As Object Dim s As String Dim i As Long Set rg = Selection 'could set in different ways Set re = CreateObject("vbscript.regexp") With re .Global = True .ignorecase = True .MultiLine = False .Pattern = "^(\S+\s+\S+)\s+([\s\S]+)\s+(Other[\s\S]+)$" End With For Each c In rg With c .Offset(0, 1).Resize(1, 3).ClearContents s = .Value If re.test(s) = True Then Set mc = re.Execute(s) For i = 0 To 2 .Offset(0, i + 1).Value = mc(0).submatches(i) Next i End If End With Next c End Sub ======================================== --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
On 3 nov, 22:17, Ron Rosenfeld wrote:
On Tue, 3 Nov 2009 12:32:17 -0800 (PST), ppeer wrote: 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 aspliton 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 thesplit. 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 Well, a different specification. I interpreted your specifications as follows: A1: * * original string B1: * * First two words of the string C1: * * Third word of the string up to but not including * * * * * * * * the word "Other" D1: * * "Other" and everything following it Given that, it was easy to build a Regular Expression to those rules, and implement it in VBA code. *(And if your specifications are different, it would be pretty straightforward to adjust the regex): ========================================== Option Explicit Sub ParseData() Dim c As Range, rg As Range Dim re As Object, mc As Object Dim s As String Dim i As Long Set rg = Selection 'could set in different ways Set re = CreateObject("vbscript.regexp") * * With re * * * * .Global = True * * * * .ignorecase = True * * * * .MultiLine = False * * * * .Pattern = "^(\S+\s+\S+)\s+([\s\S]+)\s+(Other[\s\S]+)$" * * End With For Each c In rg * * With c * * * * .Offset(0, 1).Resize(1, 3).ClearContents * * * * s = .Value * * * * If re.test(s) = True Then * * * * * * Set mc = re.Execute(s) * * * * * * For i = 0 To 2 * * * * * * * * .Offset(0, i + 1).Value = mc(0).submatches(i) * * * * * * Next i * * * * End If * * End With Next c End Sub ======================================== --ron- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Both solutions work 100%. Next time I'll put more effort in defining clear and short the specs. Ron, I am not very well known with the patern parameter. Do you know where I can find more about that topic? I'll extend the code with the input of a (word) array because the static text like Other can also be a couple of other words (but this group of words is always the same). Thanks you very much. Off the record: are you experienced with regard to excel-database- driven functionality? I am thinking about developing a database driven excel functionality. The data are stored in a central database and can be called and processed by local users with the help of an add-in and userforms. Please let me know. I am just trying to get an idea about the possibilities. best regards Peter |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
On Wed, 4 Nov 2009 01:06:26 -0800 (PST), ppeer wrote:
Both solutions work 100%. Next time I'll put more effort in defining clear and short the specs. Ron, I am not very well known with the patern parameter. Do you know where I can find more about that topic? I'll extend the code with the input of a (word) array because the static text like Other can also be a couple of other words (but this group of words is always the same). Thanks you very much. Off the record: are you experienced with regard to excel-database- driven functionality? I am thinking about developing a database driven excel functionality. The data are stored in a central database and can be called and processed by local users with the help of an add-in and userforms. Please let me know. I am just trying to get an idea about the possibilities. best regards Peter Peter, Glad its working for you. To add other possible "starting words" to the last substring, you need to alter pattern to change the Other to a parentheses enclosed, pipe-delimited set of words (or substrings). Original: ..Pattern = "^(\S+\s+\S+)\s+([\s\S]+)\s+(Other[\s\S]+)$" For example, to add "Type B" and "TypeC" as possible delimiters: ..Pattern = "^(\S+\s+\S+)\s+([\s\S]+)\s+((Other|Type B|TypeC)[\s\S]+)$" Note that you are not confined to a single word. Also, the pattern will match the first instance. So if two items are similar, you need to have the most unique first: Consider "Type AA" vs "Type A". If Type A is listed first in order, the regex will NEVER match Type AA. Also, you need to ensure that these delimiter substrings are unique, and don't appear in previous sections. Because I just upgraded to W7, I don't have access to my bookmarks regarding Regular Expressions. But I'll try to post them later when I have access. If you Google regarding Regular Expressions, there should be plenty of information. There is also a detailed description of using it in VBA on the Microsoft web site. --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
On Wed, 4 Nov 2009 01:06:26 -0800 (PST), ppeer wrote:
Off the record: are you experienced with regard to excel-database- driven functionality? I am not. But if you post your specifications, I'm sure there are those here who can help. And I'm sure there are some here that also do consulting. I would start a new thread for this. --ron |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
split cell on 2th and 3th space
On Wed, 4 Nov 2009 01:06:26 -0800 (PST), ppeer wrote:
On 3 nov, 22:17, Ron Rosenfeld wrote: On Tue, 3 Nov 2009 12:32:17 -0800 (PST), ppeer wrote: 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 aspliton 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 thesplit. 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 Well, a different specification. I interpreted your specifications as follows: A1: * * original string B1: * * First two words of the string C1: * * Third word of the string up to but not including * * * * * * * * the word "Other" D1: * * "Other" and everything following it Given that, it was easy to build a Regular Expression to those rules, and implement it in VBA code. *(And if your specifications are different, it would be pretty straightforward to adjust the regex): ========================================== Option Explicit Sub ParseData() Dim c As Range, rg As Range Dim re As Object, mc As Object Dim s As String Dim i As Long Set rg = Selection 'could set in different ways Set re = CreateObject("vbscript.regexp") * * With re * * * * .Global = True * * * * .ignorecase = True * * * * .MultiLine = False * * * * .Pattern = "^(\S+\s+\S+)\s+([\s\S]+)\s+(Other[\s\S]+)$" * * End With For Each c In rg * * With c * * * * .Offset(0, 1).Resize(1, 3).ClearContents * * * * s = .Value * * * * If re.test(s) = True Then * * * * * * Set mc = re.Execute(s) * * * * * * For i = 0 To 2 * * * * * * * * .Offset(0, i + 1).Value = mc(0).submatches(i) * * * * * * Next i * * * * End If * * End With Next c End Sub ======================================== --ron- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Both solutions work 100%. Next time I'll put more effort in defining clear and short the specs. Ron, I am not very well known with the patern parameter. Do you know where I can find more about that topic? I'll extend the code with the input of a (word) array because the static text like Other can also be a couple of other words (but this group of words is always the same). Thanks you very much. Off the record: are you experienced with regard to excel-database- driven functionality? I am thinking about developing a database driven excel functionality. The data are stored in a central database and can be called and processed by local users with the help of an add-in and userforms. Please let me know. I am just trying to get an idea about the possibilities. best regards Peter Here are some Bookmarks regarding Regular Expressions. Regular Expressions http://www.regular-expressions.info/reference.html http://support.microsoft.com/default...02&Product=vbb http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx http://msdn2.microsoft.com/en-us/library/ms974619.aspx http://www.regex-guru.info/ --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text at space | Excel Discussion (Misc queries) | |||
Split column on first space | Excel Discussion (Misc queries) | |||
split cell at first space | Excel Programming | |||
Text to columns, split at first space only | New Users to Excel | |||
Split field based on number of characters and space | Excel Worksheet Functions |