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

Try using

Data-Text to Columns...


***Please rate me.***




"vino" wrote:

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

Try this to get you started: this will parse text into one cell and numbers
in another. With data in A1, it will put results in B1,C1 etc.. Using your
sample data, 34 will be in a separate cell from the rest of the address.

HTH

Sub testParse()
Call Parse(Range("a1")) 'Test routine with data in A1
End Sub


Sub Parse(intext)

Dim mytext() As String
Dim first As Boolean
first = True

For i = 1 To Len(intext)
txt = Mid(intext, i, 1)
If IsNumeric(txt) Then
If first Then
n = n + 1
ReDim Preserve mytext(n)
mytext(n) = mytext(n) & txt
first = Not first
Else
mytext(n) = mytext(n) & txt
End If
Else
If i = 1 Then first = False
If Not first Then
n = n + 1
ReDim Preserve mytext(n)
mytext(n) = mytext(n) & txt
first = Not first
Else
mytext(n) = mytext(n) & txt
End If
End If
Next i

For i = 1 To n
Cells(1, i + 1) = mytext(i)
Next i

End Sub

"vino" wrote:

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

On 23 Aug 2006 02:58:52 -0700, "vino" wrote:

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