# text string

#### brian5857

##### Board Regular
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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi

Try
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 = 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

Code:
``````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``````

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).

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
ABCD
1110011110,1,4,5,6,786
2111100110,1,2,3,6,786
311110,1,2,344
4110010,1,453
510021
6110,122
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,",")

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

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

brian5857 said:
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?

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

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>

Replies
0
Views
239
Replies
0
Views
267
Replies
6
Views
347
Replies
2
Views
148
Replies
2
Views
219

1,203,396
Messages
6,055,161
Members
444,766
Latest member
bryandaniel5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back