Hi buddies,
Anybody knows the macros for to separate the numbers and characters in
a cell please tell me.
For example,
1000Rs1000dollor 34,sdfj street, NY
into
1000 Rs 1000 dollor 34,sdfj
street,NY
In your example you are not separating all the numbers and characters. It
seems as if the address, which includes a number, is all in one cell.
What is the variability of the entries? Is it always in the format you show,
or could there be differences?
If it is always similar to the format you show, with the numbers and characters
to be parsed only in the string preceding the <space>, and with two of each,
then the following might work.
If there is more variability in your data, or a different requirement for
parsing, then post back.
To enter the macro below, <alt-F11> opens the VB Editor. Ensure your project
is selected in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.
Before doing anything else, from the main menu bar select Tools/References and
select "Microsoft VBScript Regular Expressions 5.5"
To use the macro, select the cells you wish to parse. <alt-F8> and RUN the
macro "ParseData"
The macro will place the "split" data into the five columns to the right of
your original data.
============================================
Option Explicit
Sub ParseData()
Dim c As Range
Dim res(1 To 5) As Variant
Dim i As Long
For Each c In Selection
res(1) = REMid(c.Text, "\d+", 1)
res(3) = REMid(c.Text, "\d+", 2)
res(2) = REMid(c.Text, "\D+", 1)
res(4) = REMid(c.Text, "\D+", 2)
res(5) = REMid(c.Text, "\s.*$")
For i = 1 To 5
c.Offset(0, i).Value = res(i)
Next i
Next c
End Sub
Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array
Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection
Dim i As Long 'counter
Dim t() As String 'container for array results
' Create a regular expression object.
Set objRegExp = New RegExp
'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern
' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive
'Set global applicability.
objRegExp.Global = True
'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then
'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.
On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function
================================
--ron
