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:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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