Left Function in code

  • Thread starter Thread starter Les Stout
  • Start date Start date
L

Les Stout

Hello,

I have got a number that i need to split using code. Could somebody
please help me out.

it is always in this format 1234567-00

and i need to split it by the first 7 "1234567" in one column and then
the last two "00" in another column.
The minus "-" can be lost.

Les Stout
 
Les,

Try something like the following:

Dim S As String
Dim N1 As String
Dim N2 As String
S = "1234567-00"
N1 = Left(S, 7)
N2 = Right(S, 2)
Range("A1").Value = "'" & N1
Range("B1").Value = "'" & N2


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks very much Chip, a further question to that.
The column has approx. 4000 parts in it would it be better to loop it
down? or do you have a better suggestion. I am wanting to do it
automated, running in the background.

Your assistance is much appreciated.

Les Stout
 
You can accomplish the same thing without VBA code, using two
formulas.

=LEFT(A1,7)
and
=RIGHT(A1,2)

Copy these formulas to cells B1 and C1, and then fill down as far
as you need to go.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Try something Like this:

Sub ParseString()
Dim S As Variant
Dim r As Range
Dim c As Integer
Dim N1 As String
Dim N2 As String

c = 1
For Each S In Range("A1:A4000")
N1 = Left(S, 7)
N2 = Right(S, 2)
Cells(c, 2) = "'" & N1
Cells(c, 3) = "'" & N2

c = c + 1
Next S
End Sub
Derick



Thanks very much Chip, a further question to that.
The column has approx. 4000 parts in it would it be better to loop it
down? or do you have a better suggestion. I am wanting to do it
automated, running in the background.

Your assistance is much appreciated.

Les Stout
 
Back
Top