Delimated Text
 

Hello,

I have a column with different codes. Each code contains number+text+number
(length of characters variable).

e.g. one cell contains 435II234, other cellc ontains 87634TIC2 and so on. I
want to split this column in three dofferent columns. First column should
show initial number, second column should show the text and third column
should show the last number.
Means in the above scenario, the cells will be split like:

435 II 234
87634 TIC 2

Text to column will work for regular pattern only. Is there any way to do
this? Any help is appreciated.

Thank You

Pawan

Delimated Text
 

My guess is there is a better way, but this is what I came up with. Assuming
your code is in A1, put these formulas where indicated...

B1: =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

C1: =LEFT(SUBSTITUTE(A1,B1,""),LEN(SUBSTITUTE(A1,B1,"" ))-LEN(D1))

D1:
=MID(A1,LEN(B1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUB STITUTE(A1,B1,"")&"0123456789")),99)

You can copy these formulas down.

Rick


"Pawan" wrote in message
...
Hello,

I have a column with different codes. Each code contains
number+text+number
(length of characters variable).

e.g. one cell contains 435II234, other cellc ontains 87634TIC2 and so on.
I
want to split this column in three dofferent columns. First column should
show initial number, second column should show the text and third column
should show the last number.
Means in the above scenario, the cells will be split like:

435 II 234
87634 TIC 2

Text to column will work for regular pattern only. Is there any way to do
this? Any help is appreciated.

Thank You

Pawan

Delimated Text
 

Try these three User Defined Functions:

Function firstnumber(r As Range) As Variant
v = r.Value
l = Len(v)
firstnumber = ""
For i = 1 To l
ch = Mid(v, i, 1)
If IsNumeric(ch) Then
firstnumber = firstnumber & ch
Else
Exit For
End If
Next
End Function


Function centertext(r As Range) As Variant
v = r.Value
l = Len(v)
centertext = ""
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
centertext = centertext & ch
End If
Next
End Function


Function lastnumber(r As Range) As Variant
v = r.Value
l = Len(v)
gather = False
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
gather = True
Else
If gather Then
lastnumber = lastnumber & ch
End If
End If
Next
End Function


With data in A1, use as:

=firstnumber(A1)
=centertext(A1)
=lastnumber(A1)
--
Gary''s Student - gsnu200796

Delimated Text
 

I added this code in VB editor, but in the "Insert Function" window, I am not
able to see 'User defined functions" option.

"Gary''s Student" wrote:

Try these three User Defined Functions:

Function firstnumber(r As Range) As Variant
v = r.Value
l = Len(v)
firstnumber = ""
For i = 1 To l
ch = Mid(v, i, 1)
If IsNumeric(ch) Then
firstnumber = firstnumber & ch
Else
Exit For
End If
Next
End Function


Function centertext(r As Range) As Variant
v = r.Value
l = Len(v)
centertext = ""
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
centertext = centertext & ch
End If
Next
End Function


Function lastnumber(r As Range) As Variant
v = r.Value
l = Len(v)
gather = False
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
gather = True
Else
If gather Then
lastnumber = lastnumber & ch
End If
End If
Next
End Function


With data in A1, use as:

=firstnumber(A1)
=centertext(A1)
=lastnumber(A1)
--
Gary''s Student - gsnu200796

Delimated Text
 

This works great... Thanks Rick.. :-)

"Rick Rothstein (MVP - VB)" wrote:

My guess is there is a better way, but this is what I came up with. Assuming
your code is in A1, put these formulas where indicated...

B1: =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

C1: =LEFT(SUBSTITUTE(A1,B1,""),LEN(SUBSTITUTE(A1,B1,"" ))-LEN(D1))

D1:
=MID(A1,LEN(B1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUB STITUTE(A1,B1,"")&"0123456789")),99)

You can copy these formulas down.

Rick


"Pawan" wrote in message
...
Hello,

I have a column with different codes. Each code contains
number+text+number
(length of characters variable).

e.g. one cell contains 435II234, other cellc ontains 87634TIC2 and so on.
I
want to split this column in three dofferent columns. First column should
show initial number, second column should show the text and third column
should show the last number.
Means in the above scenario, the cells will be split like:

435 II 234
87634 TIC 2

Text to column will work for regular pattern only. Is there any way to do
this? Any help is appreciated.

Thank You

Pawan

Delimated Text
 

On Tue, 29 Jul 2008 01:19:01 -0700, Pawan
wrote:

Hello,

I have a column with different codes. Each code contains number+text+number
(length of characters variable).

e.g. one cell contains 435II234, other cellc ontains 87634TIC2 and so on. I
want to split this column in three dofferent columns. First column should
show initial number, second column should show the text and third column
should show the last number.
Means in the above scenario, the cells will be split like:

435 II 234
87634 TIC 2

Text to column will work for regular pattern only. Is there any way to do
this? Any help is appreciated.

Thank You

Pawan

Here's a macro that should do what you want. But test it on a copy of your
data first to be certain. "Undo" does not work with this macro.

1. Select your data which must be in a single column.
2. <alt-F8 opens the macro dialog box.
3. Select ParseSpecial and <RUN.

The macro will clear the two columns to the left of the original data.

If your data meets the pattern of digits-letters-digits it will be parsed into
the three columns. (If it does not meet that pattern, no action will be
taken).

As written, it requires the letters to be capitalized, and the code can occur
anywhere within the cell string. This can be changed if necessary.

To enter this macro, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

==================================
Option Explicit
Sub ParseSpecial()
Dim c As Range
Dim i As Long
Dim str As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = "(\d+)([A-Z]+)(\d+)"
If Selection.Columns.Count < 1 Then
MsgBox ("Error -- only select data in one column")
Exit Sub
End If
For Each c In Selection
str = c.Value
Range(c(1, 2), c(1, 3)).Clear
If re.test(str) = True Then
Set mc = re.Execute(str)
For i = 0 To 2
c.Offset(0, i).NumberFormat = "@"
c.Offset(0, i).Value = mc(0).submatches(i)
Next i
End If
Next c
End Sub
=============================
--ron

Delimated Text
 

Use Rick's formulas, but for the function:


User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200796


"Pawan" wrote:

I added this code in VB editor, but in the "Insert Function" window, I am not
able to see 'User defined functions" option.

"Gary''s Student" wrote:

Try these three User Defined Functions:

Function firstnumber(r As Range) As Variant
v = r.Value
l = Len(v)
firstnumber = ""
For i = 1 To l
ch = Mid(v, i, 1)
If IsNumeric(ch) Then
firstnumber = firstnumber & ch
Else
Exit For
End If
Next
End Function


Function centertext(r As Range) As Variant
v = r.Value
l = Len(v)
centertext = ""
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
centertext = centertext & ch
End If
Next
End Function


Function lastnumber(r As Range) As Variant
v = r.Value
l = Len(v)
gather = False
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
gather = True
Else
If gather Then
lastnumber = lastnumber & ch
End If
End If
Next
End Function


With data in A1, use as:

=firstnumber(A1)
=centertext(A1)
=lastnumber(A1)
--
Gary''s Student - gsnu200796

Delimated Text
 

Hi Ron,

It works perfectly.. :-)
Thanks a ton..

Pawan

"Ron Rosenfeld" wrote:

On Tue, 29 Jul 2008 01:19:01 -0700, Pawan
wrote:

Hello,

I have a column with different codes. Each code contains number+text+number
(length of characters variable).

e.g. one cell contains 435II234, other cellc ontains 87634TIC2 and so on. I
want to split this column in three dofferent columns. First column should
show initial number, second column should show the text and third column
should show the last number.
Means in the above scenario, the cells will be split like:

435 II 234
87634 TIC 2

Text to column will work for regular pattern only. Is there any way to do
this? Any help is appreciated.

Thank You

Pawan

Here's a macro that should do what you want. But test it on a copy of your
data first to be certain. "Undo" does not work with this macro.

1. Select your data which must be in a single column.
2. <alt-F8 opens the macro dialog box.
3. Select ParseSpecial and <RUN.

The macro will clear the two columns to the left of the original data.

If your data meets the pattern of digits-letters-digits it will be parsed into
the three columns. (If it does not meet that pattern, no action will be
taken).

As written, it requires the letters to be capitalized, and the code can occur
anywhere within the cell string. This can be changed if necessary.

To enter this macro, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

==================================
Option Explicit
Sub ParseSpecial()
Dim c As Range
Dim i As Long
Dim str As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = "(\d+)([A-Z]+)(\d+)"
If Selection.Columns.Count < 1 Then
MsgBox ("Error -- only select data in one column")
Exit Sub
End If
For Each c In Selection
str = c.Value
Range(c(1, 2), c(1, 3)).Clear
If re.test(str) = True Then
Set mc = re.Execute(str)
For i = 0 To 2
c.Offset(0, i).NumberFormat = "@"
c.Offset(0, i).Value = mc(0).submatches(i)
Next i
End If
Next c
End Sub
=============================
--ron

Delimated Text
 

On Sat, 9 Aug 2008 01:09:00 -0700, Pawan
wrote:

Hi Ron,

It works perfectly.. :-)
Thanks a ton..

Pawan

Glad to help. Thanks for the feedback.
--ron