split cell on 2th and 3th space

[ppeer]

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

 

[Ron Rosenfeld]

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

 

[Peter T]

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

 

[Rick Rothstein]

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

 

[ppeer]

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)











- 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

 

[Rick Rothstein]

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)

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)











- 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

 

[Ron Rosenfeld]

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

 

[ppeer]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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.aspx?scid=kb;en-us;818802&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