Code to separate two parts of a string separated by a "-"

HarryCallaghan

Board Regular
Joined
Jan 30, 2013
Messages
50
Hello.

I'm making a system to read codebar from documents and extract info from a database using this codebar. The problem is that this codebar is composed by a "serial" and a "code", both numbers separated by a bar "-". Codebars are such as "2-234", "10-543789" and strings like that. The serial number (the left partt of the string can be a number between 1 and 11 and the code part can be a number between 1 and 999999. I have this code a wrote by myself but i'm getting an error about types not matching i guess because you cant diff operate two strings :ROFLMAO:, hope someone can help me:

Code:
Sub InputCodigo()
Dim Albaran As String
Albaran = "default"
Do Until Len(Albaran) = 0
Albaran = InputBox("testing etiquetas")
If Right(Left(Albaran, 2), 1) = "-" Then

Cells(2, "J").Value = Left(Albaran, 1)
Cells(2, "K").Value = Albaran - Left(Albaran, 2)


ElseIf Right(Left(Albaran, 2), 1) <> "-" Then

Cells(2, "J").Value = Left(Albaran, 2)
Cells(2, "K").Value = Albaran - Left(Albaran, 3)
End If

Loop
End Sub
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
I am vauge on what you want the end result to do or look like. If you want to seperate the codes at the "-" into two columns try this.

Code:
Option Explicit

Sub At_The_Dash()
Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row).Select

Selection.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
  :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub

Regards,
Howard
 

HarryCallaghan

Board Regular
Joined
Jan 30, 2013
Messages
50
Thanks a lot! I'll try your code but i figured out in other way i think even easier using the built-in function InStr. I put my code in case any of you need it:

Code:
Sub InputCodigo()
Dim Albaran As String
Dim i As Integer
Dim bar As Integer



Albaran = "default"
Do Until Len(Albaran) = 0
Albaran = InputBox("testing etiquetas")

i = Len(Albaran)
bar = InStr(Albaran, "-")
If bar > 0 Then
Cells(2, "J").Value = Left(Albaran, (bar - 1))
Cells(2, "K").Value = Right(Albaran, i - bar)
End If


Loop
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,554
Members
430,295
Latest member
amdis

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
Top