PDA

View Full Version : Solved: find values separated by a slash



vzachin
12-01-2010, 07:42 PM
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
12-01-2010, 08:27 PM
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
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

vzachin
12-02-2010, 05:49 AM
hi mbarron,

nicely done. thanks

zach

kemas
12-02-2010, 12:51 PM
a very good code

Sean.DiSanti
12-02-2010, 01:03 PM
you could also do it like...
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