breaking a cell into two cells via VBA program

[Guest]

Hello,
I have a bunch of numers written in an excell sheet that I want to be able
to seperate the first two letters and move the rest to collumn B.

Ex)
Column A Column B
37001-03501
38001-03502
39001-03503
40001-03504

would become
A B
37 001-03501
38 001-03502
39 001-03503
40 001-03504

Thank you very much for any help with this,
Jordan

 

[Norman Jones]

Hi Jordan,

Select the numbers
Data | Text to columns
Check "Fixed Width"
In the Data Preview window, click between the 2nd and 3rd digits of any of
the numbers
Finish

 

[Norman Jones]

Hi Jordan,

Using VBA, try:

Sub Test()
Dim rng As Range
Dim cell As Range

Columns(2).Insert 'to blank cells in column B
Set rng = Range("A1").CurrentRegion

For Each cell In rng.Cells
cell(1, 2).Value = Mid(cell.Value, 2)
cell.Value = Left(cell.Value, 2)
Next

End Sub

 

[Norman Jones]

Hi Jordan,

Columns(2).Insert 'to blank cells in column B

is mising the word "ensure" in the appended comment and should read:

Columns(2).Insert 'to ensure blank cells in column B

 

[Tom Ogilvy]

Sub SplitValues()
Dim sStr as String, rng as Range
set rng = Range("A1:A2000")
for each cell in rng
sStr = Trim(Cell.Value)
if sStr <> "" then
Cell.Value = "'" & Left(sStr,2)
cell.offset(0,1).Value = Right(sStr,len(sStr)-1)
end if
Next
End sub

 

[Norman Jones]

Hi Jordan,

In Tom's SplitValues Sub, which I prefer to my suggestion, there is a small
typo in the line:

cell.offset(0,1).Value = Right(sStr,len(sStr)-1)

which should read:

cell.offset(0,1).Value = Right(sStr,len(sStr)-2)