Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
breaking a cell into two cells via VBA program
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
breaking a cell into two cells via VBA program
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 --- Regards, Norman "Jordan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
breaking a cell into two cells via VBA program
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 --- Regards, Norman "Jordan" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
breaking a cell into two cells via VBA program
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 --- Regards, Norman "Norman Jones" wrote in message ... 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 --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
breaking a cell into two cells via VBA program
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 -- Regards, Tom Ogilvy "Jordan" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
breaking a cell into two cells via VBA program
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) --- Regards, Norman "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modify linked cells without breaking link | Excel Discussion (Misc queries) | |||
split text in one cell into multiple cells without breaking the wo | Excel Worksheet Functions | |||
breaking text in one cell into two cells | Excel Discussion (Misc queries) | |||
Breaking a Cell | Excel Worksheet Functions | |||
Breaking a word by each alphabet and inputing it in different cells | Excel Programming |