Dear experts,
I have a worksheet containing many cells of text + numbers + spaces (the
first cell of each row), like for cell A1:
text 12 text 34 text 67 1234567 abc % ef 12345678R019827 2

where "abc", "%" and "ef" remain always the same, all others very in length
and the first set varies in text+numbers combinations (there may or may not
be numbers, and when there are, their position is aleatory). There is always
one fixed space between the "abc" and the numbers preceeding. There are
always 5 spaces between the "ef" and the numbers after that. Between the
first set of text+numbers and the numbers after (the "1234567") there is
always at minimum 2 spaces.

I would like to be able to separate each set in a different cell. To be clear:
A2: text 12 text 34 text 67
A3: 1234567
a4: abc
etc

Could you please let me know what is the most efficient and elegant way to
do this in VBA?
Many thanks.
Best regards

Valeria

Valerie,

Whether this is elegant or not I'll let you decide but one way is to right
click the sheet tab, view code, paste this in and run it. None numbers will
extract to column B and numbers to column C.

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
Next
End Sub

Mike

"Valeria" wrote:

Dear experts,
I have a worksheet containing many cells of text + numbers + spaces (the
first cell of each row), like for cell A1:
text 12 text 34 text 67 1234567 abc % ef 12345678R019827 2

where "abc", "%" and "ef" remain always the same, all others very in length
and the first set varies in text+numbers combinations (there may or may not
be numbers, and when there are, their position is aleatory). There is always
one fixed space between the "abc" and the numbers preceeding. There are
always 5 spaces between the "ef" and the numbers after that. Between the
first set of text+numbers and the numbers after (the "1234567") there is
always at minimum 2 spaces.

I would like to be able to separate each set in a different cell. To be clear:
A2: text 12 text 34 text 67
A3: 1234567
a4: abc
etc

Could you please let me know what is the most efficient and elegant way to
do this in VBA?
Many thanks.
Best regards

Valeria

On Fri, 9 Nov 2007 05:40:02 -0800, Valeria
wrote:

Dear experts,
I have a worksheet containing many cells of text + numbers + spaces (the
first cell of each row), like for cell A1:
text 12 text 34 text 67 1234567 abc % ef 12345678R019827 2

where "abc", "%" and "ef" remain always the same, all others very in length
and the first set varies in text+numbers combinations (there may or may not
be numbers, and when there are, their position is aleatory). There is always
one fixed space between the "abc" and the numbers preceeding. There are
always 5 spaces between the "ef" and the numbers after that. Between the
first set of text+numbers and the numbers after (the "1234567") there is
always at minimum 2 spaces.

I would like to be able to separate each set in a different cell. To be clear:
A2: text 12 text 34 text 67
A3: 1234567
a4: abc
etc

Could you please let me know what is the most efficient and elegant way to
do this in VBA?
Many thanks.
Best regards

Valeria

Your description is not entirely clear to me.

However, assuming you literally mean what you write with " "abc", "%" and "ef"
remain always the same" and these are not tokens for something else, then the
following will parse the data into the rows below the selected cell. If they
are tokens, a better description can be incorporated into the routine.

It is preliminary due to lack of knowledge of your worksheet layout, and also
of some of the details missing in your data description.

For the part of the string beginning with "abc", I have assumed you wanted
separate rows for each space delimited field.

For the part prior to the "abc", I have assumed you wanted the split as you
wrote above in A2, A3

The macro uses regular expressions to capture each part of your text string, so
each part can be returned separately.

As written, only a single cell can be selected; and the rows below the cell
being parsed are NOT being cleared (since I did not know how far down it would
be safe to go).

=================================================
Option Explicit

Sub ParseText()
Dim c As Range
Dim re As Object
Dim mc As Object
Dim I As Long

Set c = Selection
If c.Count < 1 Then Exit Sub

Set re = CreateObject("vbscript.regexp")
re.Pattern = "((\w+\s)+\w+)\s+(\d+)\s(abc)\s{2}(%)\s(ef)\s{5}(\ w+)\s+(\w+)"
Set mc = re.Execute(c.Text)

c.Offset(1, 0) = mc(0).submatches(0)
For I = 3 To mc(0).submatches.Count
c.Offset(I - 1, 0).Value = mc(0).submatches(I - 1)
Next I

End Sub
===============================
--ron

Hi Mike,
this is definitely very elegant!!!!, but unfortunately it does not solve my
problem as I need a different cell for each set of characters... and with
this macro all text is in one cell, and all numbers in another one.
Many thanks anyway for your help and have a nice we!
Kind regards
--
Valeria


"Mike H" wrote:

Valerie,

Whether this is elegant or not I'll let you decide but one way is to right
click the sheet tab, view code, paste this in and run it. None numbers will
extract to column B and numbers to column C.

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
Next
End Sub

Mike

"Valeria" wrote:

Dear experts,
I have a worksheet containing many cells of text + numbers + spaces (the
first cell of each row), like for cell A1:
text 12 text 34 text 67 1234567 abc % ef 12345678R019827 2

where "abc", "%" and "ef" remain always the same, all others very in length
and the first set varies in text+numbers combinations (there may or may not
be numbers, and when there are, their position is aleatory). There is always
one fixed space between the "abc" and the numbers preceeding. There are
always 5 spaces between the "ef" and the numbers after that. Between the
first set of text+numbers and the numbers after (the "1234567") there is
always at minimum 2 spaces.

I would like to be able to separate each set in a different cell. To be clear:
A2: text 12 text 34 text 67
A3: 1234567
a4: abc
etc

Could you please let me know what is the most efficient and elegant way to
do this in VBA?
Many thanks.
Best regards

Valeria