Can anyone help me figure out what I have wrong here. I think the code
shows what I am trying to do. The problem with simply using a Left()
or Right() function is the data size changes, of course and I am
splitting the data. I know this will work, but I can't get it to. I
keep getting the error: Sub or function not defined for Find.

strDWG = Range("J" & i & "").Value
'dwgNSN = Left(strDWG, 18)
dwgNSN = Left(strDWG, Find("/", strDWG) - 1)
'dwgMFR = Right(strDWG, 18)
dwgMFR = Right(strDWG, Find("/", strDWG) + 1)

You did not mention exactly what you were trying to do but if you are trying
to split a string based on a delimeter (bacslash in this case) then you could
just use the split function which returns an array of the component peices of
the string...
--
HTH...

Jim Thomlinson


"Glen" wrote:

Can anyone help me figure out what I have wrong here. I think the code
shows what I am trying to do. The problem with simply using a Left()
or Right() function is the data size changes, of course and I am
splitting the data. I know this will work, but I can't get it to. I
keep getting the error: Sub or function not defined for Find.

strDWG = Range("J" & i & "").Value
'dwgNSN = Left(strDWG, 18)
dwgNSN = Left(strDWG, Find("/", strDWG) - 1)
'dwgMFR = Right(strDWG, 18)
dwgMFR = Right(strDWG, Find("/", strDWG) + 1)

Thanks Jim and I saw the Split Function but I was not sure how I would
get the values split into dwgNSN and dwgMFR. I have data that looks
like 9I1234-01-234-5678 / 8H1234-02-345-6789. I need to get the data
on either side of the slash because sometimes my data looks like :
9I1234- / 8H1234-02-345-6789 or even - / -. I need to evaluate these
strings later for validity. Thanks.

This should be close to what you want...

Sub SplitString()
Dim aryStuff() As String
Dim strThis As String
Dim strThat As String

aryStuff = Split("9I1234-01-234-5678 / 8H1234-02-345-6789", "/")
strThis = Trim(aryStuff(0))
strThat = Trim(aryStuff(1))

MsgBox strThis
MsgBox strThat
End Sub
--
HTH...

Jim Thomlinson


"Glen" wrote:

Thanks Jim and I saw the Split Function but I was not sure how I would
get the values split into dwgNSN and dwgMFR. I have data that looks
like 9I1234-01-234-5678 / 8H1234-02-345-6789. I need to get the data
on either side of the slash because sometimes my data looks like :
9I1234- / 8H1234-02-345-6789 or even - / -. I need to evaluate these
strings later for validity. Thanks.

Hello Glen,

Have you tried using the InStr function? Here is your code with it
inserted...

strDWG = Range("J" & i & "").Value
'dwgNSN = Left(strDWG, 18)
dwgNSN = Left(strDWG, InStr(1, "/", strDWG) - 1)
'dwgMFR = Right(strDWG, 18)
dwgMFR = Right(strDWG, Len(strDWG) - InStr(1, "/", strDWG) )

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=505850

Thank you both. I was just using the InStr function while you two were
replying to see if it would work for me and it seems to do the job. I
am going to try the split just to see how it goes and keep it for
future reference. Thanks again to both of you.

Looking at your origingal question I was initailly unsure just how many items
you needed to get from your string. For 2 the Instr function is great. For
more I use the split function, but both work just fine...
--
HTH...

Jim Thomlinson


"Glen" wrote:

Thank you both. I was just using the InStr function while you two were
replying to see if it would work for me and it seems to do the job. I
am going to try the split just to see how it goes and keep it for
future reference. Thanks again to both of you.