Excel 2000 VBA - Using Split() with no Lelimiter in string
 

I have a string, called badNumber, that is a non-delimited concatenatio
of multple values. If there is NO delimiter, can I delimit by number o
characters:

For Instance:

_Column_A_
1234567
1234567
1234567
12345671234567
12345671234567
123456712345671234567

Thanks!
Mar

--
Message posted from http://www.ExcelForum.com

Excel 2000 VBA - Using Split() with no delimiter in string
 

I can get the left 7 characters out with Left(badNumber, 7) but is ther
a function that will CUT these out of the cell

--
Message posted from http://www.ExcelForum.com

Excel 2000 VBA - Using Split() with no delimiter in string
 

Well, Id really need more information to give you a proper answer, bu
this should get you started in any case. If you know that the piece
are a certain length and that they are all the same length you could d
something like this assuming the pieces are 7 characters long:

var1 = Cells(1, 1).Value
For x = 1 To Len(var1) / 7 <-- Tells you how many pieces you have
pieceVar = Left(var1, 7) <-- Assigns the value of the first piece
var1 = Right(var1, Len(var1) - 7) <-- Removes 1st piece from origina
variable
<(Use this space to do what you need to with the first piece)
Next x

You might be able to make use of Mid() though I'd tend to doubt it i
you are dealing with pieces of different lengths.

- Piku

--
Message posted from http://www.ExcelForum.com

Excel 2000 VBA - Using Split() with no delimiter in string
 

I went with this:

Sub NumberMassage()
Range("A:E").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending
Range("A1").Select
While ActiveCell 9999999
Range("A1").Select
badNum = ActiveCell.Value
newNum = Left(badNum, 7)
badNum = Trim(Right(badNum, Len(badNum) - 7))
ActiveCell = [badNum]
Selection.EntireRow.Copy
Selection.EntireRow.Insert
Selection.EntireRow.PasteSpecial
Range("A1").Select
ActiveCell = [newNum]
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending
Wend
End Sub

I got the Trim function from another recent post

--
Message posted from http://www.ExcelForum.com

Excel 2000 VBA - Using Split() with no delimiter in string
 

Try this. I haven't tested it, but it works in theory and it should b
quite a bit faster:

Sub NumberMassage()
Application.ScreenUpdating = False
Range("A:E").Sort Key1:=Range("A1"), Order1:=xlDescending
While Cells(1, 1).Value 9999999
badNum = Cells(1, 1).Value
newNum = Left(badNum, 7)
badNum = Trim(Right(badNum, Len(badNum) - 7))
ActiveCell.Value = badNum
Rows(1).Copy
Rows(1).Insert
Cells(1, 1).Value = newNum
Wend
Range("A:E").Sort Key1:=Range("A1"), Order1:=xlDescending
Application.ScreenUpdating = True
End Sub

I should add that the reason it should be faster is that selectin
cells takes up alot of time when you do it as many times as you will b
here and updating the screen with every one of those changes takes LOT
of time. By not updating the screen and avoiding unnecessar
selections you can increase your speed immensely. - piku

--
Message posted from http://www.ExcelForum.com