text string

[brian5857]

Here is one I just can not get. In cell A1 I have a text string. It will be a binary value with a length from 1 to 8 characters.

For example: 1001001 or 01101 or 11110000 ...

In need a user defined formula in cell B1 that will display the locations of the 1's in the text string, reading from right to left with 0 being the first location on the right.

For example if I have this text string in cell A1: 1001111
Cell B1 should display the following: 0, 1, 2, 3, 6

I got this from member "HotPepper" (thanks for the help)

formula in cell B1:
--------------------------------------------------------------------------------
=binpos(A1,1) to find the 1s

or

=binpos(A1,0) to find the 0s

if the second number is not provided, it will find the 0s.

code:
--------------------------------------------------------------------------------
Public Function BinPos(s As String, Optional num As Integer)
If num <> 0 Then num = 1
Dim x As Integer, b As String
For x = 1 To Len(s)
If Mid(s, x, 1) = num Then b = b & x-1 & " "
Next x
BinPos = Replace(Trim(b), " ", ",")
End Function

This code got me close but read from left to right.

 

[acw]

Hi

Try

Public Function BinPos(s As String, Optional num As Integer)
If num <> 0 Then num = 1
Dim x As Integer, b As String
For x = Len(s) To 1 Step -1
If Mid(s, x, 1) = num Then b = b & Len(s) - x & " "
Next x
BinPos = Replace(Trim(b), " ", ",")
End Function

Tony

 

[jindon]

Function binpos(s As Variant, Optional flg As Boolean = True) As String
Dim num As Integer
num = 0
If flg Then num = 1
s = StrReverse(CStr(s))
For i = 1 To Len(s)
    If Mid$(s, i, 1) = num Then _
        binpos = binpos & i - 1 & ", "
Next
binpos = Left(binpos, Len(binpos) - 2)
End Function

 

[venkat1926]

this may not be an elgant solution but based more or less on your macro
assuming it is always only 6 characters long
run this sub

Public Sub test()
Dim str As String
str = Range("a1").Value
Dim i As Integer
Dim j As String
i = 6
j = ""
line1:
'MsgBox i
If i = 0 Then GoTo line2
If Mid(str, i, 1) = "1" Then
If i = 1 Then GoTo line3
j = j & i & ","
'MsgBox j
i = i - 1

GoTo line1
Else
i = i - 1
GoTo line1

End If
line3:
j = j & i
line2:
Range("B1") = j

MsgBox "macro over"
End Sub

change tosuit you.
if you have nu.mber of such strings in column A do a loop and park j in acivecell.offset(0,1).

 

[Yogi Anand]

Hi Brian:

Let us have a look at my convoluted formula based approach ... I don't think you will want to use it ... but anyway here it is ...

Book1
	A	B	C	D
1	11001111	0,1,4,5,6,7	8	6
2	11110011	0,1,2,3,6,7	8	6
3	1111	0,1,2,3	4	4
4	11001	0,1,4	5	3
5	10	0	2	1
6	11	0,1	2	2
Sheet6

I have used two computational columns C and D, and I have used MCONCAT function from the MoreFunc Add-in.

formula in cell C1 is ... =Len(A1)
formula in cell D1 is ... =LEN(SUBSTITUTE(A1,0,""))
and
array formula in cell B1 is ...

=MCONCAT(SMALL(IF(ISERROR(FIND(MID(A1,ROW(INDIRECT("1:"&C1)),1),1)*ROW(INDIRECT("1:"&C1))),"",FIND(MID(A1,ROW(INDIRECT("1:"&C1)),1),1)*ROW(INDIRECT("1:"&C1))),ROW(INDIRECT("1:"&D1)))-1,",")

 

[brian5857]

Thanks for the help. Worked perfect. This is a great forum.

 

[brian5857]

I cant get the array formula to work. Keep getting #name? in cell B1

 

[Yogi Anand]

I cant get the array formula to work. Keep getting #name? in cell B1

Hi Brian:

An array formula is commited with CTRL+SHIFT+ENTER rather than with just ENTER ... did you enter and commit the formula correctly?

 

[just_jon]

I cant get the array formula to work. Keep getting #name? in cell B1

MCONCAT is part of the Morefunc add-in, a free download available here.

You can substitute ACONCAT [ posted below ] for it if needed.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> aconcat(a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> sep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "") <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">' Harlan Grove, Mar 2002</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> a <SPAN style="color:#00007F">Is</SPAN> Range <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> y <SPAN style="color:#00007F">In</SPAN> a.Cells
            aconcat = aconcat & y.Value & sep
        <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">ElseIf</SPAN> IsArray(a) <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> y <SPAN style="color:#00007F">In</SPAN> a
            aconcat = aconcat & y & sep
        <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">Else</SPAN>
        aconcat = aconcat & a & sep
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

</FONT>