Quote from syswizard:
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
