Working with SPLIT - Error 9

[blelli]

Dears,

Another question...

I've to collect 2 information that is CAR and TYPE, and the user can insert it in different ways, like:

BMW
BMW/SEDAN
/SEDAN

So, everything before the slash is the CAR and after the slash is the TYPE.

I'm trying tho use the expression Split, but when there is nothing after the slash "/", I'm getting the error 9.

How can I do it?

Thank you

 

[Nishant94]

Assuming your data is in A1:

For CAR:

=LEFT(A1&"/",FIND("/",A1&"/")-1)

FOR TYPE:

=MID(A1,FIND("/",A1&"/")+1,255)

OR,

=SUBSTITUTE(A1,B1&"/","")

This one assumes that you are putting the formula of car starting from B1

 

[blelli]

Assuming your data is in A1:

For CAR:

=LEFT(A1&"/",FIND("/",A1&"/")-1)

FOR TYPE:

=MID(A1,FIND("/",A1&"/")+1,255)

OR,

=SUBSTITUTE(A1,B1&"/","")

This one assumes that you are putting the formula of car starting from B1

Thank you so much,
But I need it in VBA Code... woudl you mind to translate it for me?

Thank you

 

[Fluff]

You can check if a / occurs like

If InStr(1, Range("A1"), "/") > 0 Then
   MsgBox Split(Range("A1"), "/")(1)
End If

 

[blelli]

You can check if a / occurs like

If InStr(1, Range("A1"), "/") > 0 Then
   MsgBox Split(Range("A1"), "/")(1)
End If

I don't think it's gonna help me, because:
You can have the slash "/" in both cases, like:

BMW/
or
/SEDAN

So the fact is that I would like to get all the values left and right of a "/" and store it in 2 different vars.

How can I do it?
Thank you

 

[Fluff]

What code have you already got?

 

[ZVI]

May be this:

Sub Test()
  
  Dim a As Variant
  Dim i As Long
  Dim Car As String, CarType As String, s As String
  
  ' Test string, it can be s = Range("A1").Value
  s = "BMW" & vbLf & "BMW/SEDAN" & vbLf & "/SEDAN"
  
  a = Split(s, vbLf)
  For i = 0 To UBound(a)
    If InStr(a(i), "/")>1 Then
      Car = Split(a(i), "/")(0)
      CarType = Split(a(i), "/")(1)
      Debug.Print "Car=" & Car, "Type=" & CarType
      Exit For
    End If
  Next
    
End Sub

 

[mikerickson]

One quick way to deal with a missing terminal delimiter is to add it explicitly.

MsgBox "Type is " & Split(myString & "/", "/")(1)

 

[ZVI]

The another case:

Sub Test1()
' The case there is no vbLf character in the string
 
  Dim a As Variant
  Dim i As Long
  Dim Car As String, CarType As String, s As String
 
  ' Test string, it can be s = Range("A1").Value
  s = "BMW"
  ' Uncomment the below testing data
  's = "BMW/SEDAN"
  's = "/SEDAN"
  's = "BMW/"
 
  a = Split(s, "/")
  If UBound(a) = 0 Then
     Car = Trim(a(0))
  Else
    If Len(Trim(a(0))) > 0 Then Car = a(0)
    If Len(Trim(a(1))) > 0 Then CarType = a(1)
  End If
 
  Debug.Print "Car=" & Car, "Type=" & CarType
 
End Sub

 

[Rick Rothstein]

See if this helps any (using cell A1 for example purposes)...

Sub Test()
  Dim Data As String, Car As String, BodyType As String, CarType() As String
  CarType = Split(Range("A1").Value, "/")
  If CarType(0) = "" Then
    Car = "No Car Specified"
  Else
    Car = CarType(0)
  End If
  If UBound(CarType) = 0 Then
    BodyType = "No Type Specified"
  Else
    BodyType = CarType(1)
  End If
  MsgBox "Make: " & Car & vbLf & "Type: " & BodyType
End Sub