I have a column in Excel containing strings of positive integers with before and after a string of zeros. For example: 00012301200123456780000001230100001234500. What I want to do is to revert the string of integers between the zeros. The above example will end in: 00032102100876543210000003210100005432100. Any idea how to program this in Excel? Thanks in advance. Hans
Lordie- please tell me why you posed this question ? Was this a test for quant-types like me or what ? Is there really a PRACTICAL problem to be solved here ? Here is the solution: It is good up to 100 characters in length..... Place the code below the asterisks into a Module. Hint: Hit F11 key, and then do an "Insert Module" from the Menu Bar. On the worksheet, make sure the source column is formatted as text as well as the target column. in the target column, enter this in the first row: =ReverseAtZero($A1) Copy this cell to all cells below that need to be converted. (Note: this assumes your source data is in Column "A"...if not, please change to the appropriate column letter) ********************************************** Public Function ReverseAtZero(pRng As Range) As String Const MAX_CHARS = 100 Application.Volatile Dim rngData As Range, strIn As String, strOut As String Dim ar_init(1 To MAX_CHARS) As Byte, ar_final(1 To MAX_CHARS) As Byte Dim bZero_Char As Boolean, bChar_Zero As Boolean Dim i As Long, j As Long, lStartZ As Long, lEndZ As Long, k As Long Dim iZero As Integer iZero = Asc("0") strIn = pRng.Value If Len(strIn) = 0 Then Exit Function For i = 1 To Len(strIn) ar_init(i) = Asc(Mid(strIn, i, 1)) Next For i = 1 To Len(strIn) bZero_Char = ar_init(i) = iZero And ar_init(i + 1) <> iZero bChar_Zero = ar_init(i) <> iZero And ar_init(i + 1) = iZero If bZero_Char Then lStartZ = i + 1 _ Else: If lStartZ > 0 And bChar_Zero Then lEndZ = i If lStartZ > 0 And lEndZ > 0 Then k = 0 For j = lEndZ To lStartZ Step -1 ar_final(k + lStartZ) = ar_init(j) k = k + 1 Next lEndZ = 0: lStartZ = 0 Else ar_final(i) = ar_init(i) End If Next ReverseAtZero = ByteArrayToString(ar_final()) End Function Public Function ByteArrayToString(ByRef bytArray() As Byte) As String Dim sAns As String, iPos As Long sAns = StrConv(bytArray, vbUnicode) iPos = InStr(sAns, Chr(0)) If iPos > 0 Then sAns = Left(sAns, iPos - 1) ByteArrayToString = sAns End Function
Using R, this would collapse to a single line: > a <- "00012301200123456780000001230100001234500" > > paste(lapply(lapply(strsplit(strsplit(paste(a,"0",sep=""),"0")[[1]],""),rev), paste, collapse=""), collapse="0") [1] "00032102100876543210000003210100005432100" >
Just to be sure. The individual cells in the given example are: A[1]=0 A[2]=0 A[3]=0 A[4]=1 A[5]=2 A[6]=3 A[7]=0 etc. I want a result like: B[1]=0 B[2]=0 B[3]=0 B[4]=3 B[5]=2 B[6]=1 B[7]=0 etc. Syswizard, is your function doing this? Thanks.
00012301200123456780000001230100001234500 00032102100876543210000003210100005432100 123012001234567800000012301000012345 123021008765432100000032101000012345 012301200123456780123010123450 032102100876543210321010543210 These are the before/after test results.
Exactly....he never mentioned each digit was in a SEPARATE CELL. However, with VBA, it should not be a huge modification. But at this point, I wonder if he is just "playing" here. I haven't heard one word as to how and why the data is so strangely arranged like this.
Guys, don't be suspicious. I was asking for some serious help. Sorry, for the misunderstanding. I will give some background for my question. I have an Excel sheet with a column displaying the consecutive wins minus one. This column contains a zero or a positive integer. Each integer is followed by the previous integer plus one or a zero. I have some open positions also and a position should be closed only when the consecutive wins is ending (when there is a zero). How can I calculate the result of each trade? Some trades will take one, two, three or even eight days. I know I can use in Excel the command "Offset" for scrolling up and down in a column. If I have four consecutive wins, it's showing up after the fourth trade. This number should be showing up at the first trade, the second trade should show a three and so on. That the reason why I was asking. But maybe there are better solutions. Thanks for your response so far. Looking forward to see a solution. Hans
At this point, explaining this is fruitless...why don't you attach a sample of the actual XLS file for us to review and work-with. That way, there are no misunderstandings.