Solved: find values separated by a slash

[vzachin]

hi,

i have a column of data that is 6 characters in length and possibly separated by a slash (/) or a dash (-).

if there is a slash or a dash,
is there a vba solution that will give me the value of the data on either side of the slash or dash?

i would then use the left value and right value to run some other code.

eg :

1/2345 would be 1 and 2345

12/345 would be 12 and 345

i can use a formula in 2 columns but i rather do this with vba.

thanks
zach

[mbarron]

This will check all cells in column A and split the cells with a / or -, placing the data in the F and G columns as your example shows
[VBA]Sub split()
Dim rSplit As Range, rACol As Range, sDelim As String
Application.ScreenUpdating = False
Set rACol = Range("A1:A" & Range("a" & Rows.Count).End(xlUp).Row)
For Each rSplit In rACol
sDelim = ""
If InStr(1, rSplit, "/") > 0 Then
sDelim = "/"
End If

If InStr(1, rSplit, "-") > 0 Then
sDelim = "-"
End If
If sDelim = "" Then
rSplit.Offset(0, 5) = rSplit
Else
rSplit.Offset(0, 5) = Left(rSplit, InStr(1, rSplit, sDelim) - 1)
rSplit.Offset(0, 6) = Mid(rSplit, InStr(1, rSplit, sDelim) + 1)
End If
Next
Application.ScreenUpdating = True
End Sub[/VBA]

[vzachin]

hi mbarron,

nicely done. thanks

zach

[kemas]

a very good code

[Sean.DiSanti]

you could also do it like...
[vba]x = 2;starting point assuming you have headers
While Activesheet.Range("a" & x).formula <> "";loop until there's nothing in the a column
tmp = Split(Replace(Activesheet.Range("a" & x).formula,"-","/"),"/"); split your string into elements
activesheet.range("f" & x).formula = tmp(0);put the first one in f
activesheet.range("g" & x).formula = tmp(1); and the second in g
x = x + 1;increment your iterator
Wend;do it again
[/vba]