splitting a string

[roc616]

I'm new to VBA and running into problem on what is probably a very simple
task. Any help would be greatly appreciated.

I have information in a cell like this: 4.5/FNCL 5.

I need to copy the information before the / to another cell, and the
information after the / to a different cell.

This is what I have been doing:

dim coupon
dim collat

coupon = Mid(Sheets("temp").Cells(row, 5).Value, 1, Find("/",
Sheets("temp").Cells(row, 5).Value, 1) - 1)

collat = Mid(Sheets("temp").Cells(row, 5).Value, Find("/",
Sheets("temp").Cells(row, 5).Value, 1) + 1, Len(Sheets("temp").Cells(row,
5).Value))

the formula using mid and find works when i put in a cell in excel, but it's
not working in the VBA. when i click debug, it highlights the word "find"
saying it doesn't recognize the function. Does anyone know a different way
of doing it or what could be wrong?

Thank you.

 

[Gary''s Student]

Use the SPLIT() function to split a string. Here is an example with your
value in A1:

Sub roc616()
v = Range("A1").Value
s = Split(v, "/")
Range("A2").Value = s(0)
Range("A3").Value = s(1)
End Sub

This will put the two halves in A2 & A3

 

[Don Guillett]

Look in the vba help index for
INSTR

Sub splittext()'this is the idea
With Cells(2, "J")
x = InStr(.Value, "/")
lp = Left(.Value, x - 1)
MsgBox lp
rp = Mid(.Value, x + 1, 33)
MsgBox rp
End With
End Sub

 

[Dave Peterson]

If you have a bunch of these entries in a single column, you could insert a
column to the right and then use:

select the range
Data|text to columns
delimited (by /)

And you'll get the info in different cells really quickly.

You could record a macro when you do it manually if you need the code.

 

[sivak]

Try this.

Dim coupon
Dim collat, pos As Integer

cellval = Sheets("temp").Cells(Row, 5).Value
pos = InStr(cellval, "/")

If pos > 0 Then
collat = Mid(cellval, pos + 1)
coupon = Mid(cellval, 1, pos - 1)
MsgBox coupon & ", " & collat
End If

 

[Gary Keramidas]

just in addition to gary's student's code, it code also be done like this:

v = Range("A1").Value
Range("A2").Value = Split(v, "/")(0)
Range("A3").Value = Split(v, "/")(1)