VBA Routine to split cell data...

[ajayb]

Hi all,

I have the following in cell a2: AB/9999/08 The number of characters before
the first / can increase. The number of characters between the slashes can
range from 1 - 5 characters.

I have used the following functions in my sheet to break down the info (with
the help of this site!):

b2: =LEFT(A2,FIND("/",A2)-1) gives AB
c2: =MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)*1
gives 9999
d2: =RIGHT(A2,(LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))) gives 08

I would now like to automate all that with a routine to run until it reaches
a blank cell in column A!

I have a routine to do the first one, which I can post if anyone wants to
see it.

Looking forward to responses!

Regards

Andy

 

[Barb Reinhardt]

Try something like this
Dim myRange as range
Dim lRow as long
Dim r as range

lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count,1).end(xlup).row
set myRange = ActiveSheet.Cells(2,1).resize(lrow-2+1,1)

For each r in myRange
'Do all of your work on r.text
next r

 

[pbartleby]

You can use Text to Columns with the delimiter set to "/". Something
like this:

Public Sub TTC_OtherDelimiter()

Dim strDelimiter As String

strDelimiter = "/"

Columns("A:A").TextToColumns _
Destination:=Range("B1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Other:=True, OtherChar:=strDelimiter, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
TrailingMinusNumbers:=True

End Sub

-PB

 

[ajayb]

Hi Barb,

Many thanks, but could I just ask what you mean by:

'Do all of your work on r.text

Regards

Andy

 

[Barb Reinhardt]

Your going to be looping through the values in column A.

First it will be A2, then A3, then A4 until you get to the end of data in
column A. I presume you are parsing data from column A and putting that
info in columns B, C, and D. You need to get the value from column A to do
that.

Make sense?

 

[Rick Rothstein \(MVP - VB\)]

This macro should do what you asked....

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
If UBound(Sections) = 2 Then
For Z = 1 To 3
.Cells(X, Z + 1).Value = Sections(Z - 1)
Next
End If
Next
End With
End Sub


Rick

 

[ajayb]

Hi Rick,

Many thanks, that is great and does the job perfectly!

One little addition though, if I may? What can I add to it to make it work
on the following entry in a cell in column a:

DC/?

This is where only the first 2 characters are known. When I run your macro
it leaves cells B, C and D blank.

Regards

Andy

 

[Rick Rothstein \(MVP - VB\)]

My code was based on the cells needing 3 parts to qualify for parsing. To
handle any text (including text with more than 2 slashes), try this...

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub


Rick

 

[ajayb]

Hi Rick,

That's absolutely brilliant. Does the job perfectly.

Thanks you very much for your help.

Regards

Andy

 

[ajayb]

Thanks for all your help Barb.

Regards

Andy

Your going to be looping through the values in column A.

First it will be A2, then A3, then A4 until you get to the end of data in
column A. I presume you are parsing data from column A and putting that
info in columns B, C, and D. You need to get the value from column A to do
that.

Make sense?

 

[ajayb]

Hi there,

Many thanks for your help.

Regards

Andy