Delimated Text

P

Pawan

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
 
R

Rick Rothstein \(MVP - VB\)

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},SUBSTITUTE(A1,B1,"")&"0123456789")),99)

You can copy these formulas down.

Rick
 
G

Gary''s Student

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)
 
P

Pawan

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

Pawan

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

Rick Rothstein (MVP - VB) said:
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},SUBSTITUTE(A1,B1,"")&"0123456789")),99)

You can copy these formulas down.

Rick
Click to expand...
 
R

Ron Rosenfeld

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
Click to expand...

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
 
G

Gary''s Student

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/WritingFunctionsInVBA.aspx
for specifics on UDFs
 
P

Pawan

Hi Ron,

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

Pawan

Ron Rosenfeld said:
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
Click to expand...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Some Cells display text others dates in the same column 2
If cell contains text then ...... 5
Restating question regarding conditional sum from multiple columns 2
Conditional Format of Text 1
Text to Column Function Rejects Zeros 4
Is there away around an imported reports funky layout? 3
SUMIF with OFFSET 5
Running VB Code 1

Top