Working with SPLIT - Error 9

B

blelli

Board Regular
Joined
Jul 21, 2013
Messages
73
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Assuming your data is in A1:

For CAR:
Code: 
=LEFT(A1&"/",FIND("/",A1&"/")-1)

FOR TYPE:

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

OR,

Code: 
=SUBSTITUTE(A1,B1&"/","")
This one assumes that you are putting the formula of car starting from B1
 
Upvote 0
Nishant94 said:
Assuming your data is in A1:

For CAR:
Code: 
=LEFT(A1&"/",FIND("/",A1&"/")-1)

FOR TYPE:

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

OR,

Code: 
=SUBSTITUTE(A1,B1&"/","")
This one assumes that you are putting the formula of car starting from B1
Click to expand...


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

Thank you
 
Upvote 0
You can check if a / occurs like
Code: 
If InStr(1, Range("A1"), "/") > 0 Then
   MsgBox Split(Range("A1"), "/")(1)
End If
 
Upvote 0
Fluff said:
You can check if a / occurs like
Code: 
If InStr(1, Range("A1"), "/") > 0 Then
   MsgBox Split(Range("A1"), "/")(1)
End If
Click to expand...


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
 
Upvote 0
What code have you already got?
 
Upvote 0
May be this:
Rich (BB code): 

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
 
Last edited:
Upvote 0
One quick way to deal with a missing terminal delimiter is to add it explicitly.

Code: 
MsgBox "Type is " & Split(myString & "/", "/")(1)
 
Upvote 0
The another case:
Rich (BB code): 
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
 
Last edited:
Upvote 0
See if this helps any (using cell A1 for example purposes)...
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Clipboard error when copying shape
Replies
4
Views
421
marc81
M
P
  • Solved
Email macro compile error
Replies
3
Views
335
duggie33
D
G
  • Question
Help with writing VBA for data entry form
Replies
2
Views
800
GA036872
G
D
  • Question
Excel Grid lines
Replies
0
Views
354
daleEH
D
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
807
RoryA
RoryA

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top