Inverting bits

[iinlayo]

Let me be more specific. For example I have 1011 0100, what I want is to invert the second bit from 0 to 1 or from 1 to 0.

10110100 = 11110100
11110100 = 10110100

I have 5000 rows of different 8-bit patterns and would like to invert the second bit from the 1st row to the 5000th row. Thanks.

 

[Hooi]

Let me be more specific. For example I have 1011 0100, what I want is to invert the second bit from 0 to 1 or from 1 to 0.

10110100 = 11110100
11110100 = 10110100

I have 5000 rows of different 8-bit patterns and would like to invert the second bit from the 1st row to the 5000th row. Thanks.

Try this
=IF(SEARCH(0,A1,2)=2,REPLACE(A1,2,1,1),REPLACE(A1,2,1,0))

 

[pgc01]

Try:

=REPLACE(A1,2,1,1-MID(A1,2,1))

 

[iinlayo]

what if i want to invert the 2nd bit, 4th bit and the 6th bit?
example :

10111010 = 11101110? thanks

 

[Hooi]

what if i want to invert the 2nd bit, 4th bit and the 6th bit?
Example :

10111010 = 11101110? Thanks

=if(rightb(a1,1)="0",11111110-a1+10101010,11111111-a1+10101010)

 

[iinlayo]

i tried it, from 10110111 it became 11102010.

 

[Hooi]

i tried it, from 10110111 it became 11102010.

Lol sorry for didnt try for 3rd 5th and 7th digit with 0...i trying update and see

 

[yytsunamiyy]

Try with the following UDF:

Public Function INVERTBITS(Source As Range, bits_to_invert As String) As String
Dim i As Integer
Dim store As String, x as string
 
For i = 1 To Len(Source.Value)
    x = i
 
        If InStr(bits_to_invert, x) > 0 Then
            If Mid(Source.Value, i, 1) = "0" Then
                store = store & "1"
            Else
                store = store & "0"
 
            End If
        Else
             store = store & Mid(Source.Value, i, 1)
             On Error GoTo 0
        End If
 
Next i
INVERTBITS = store
 
End Function

Usage:

<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=313><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=80>Source-Byte </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=80>Result </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 115pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=153>Formula in B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>110</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>=INVERTBITS(A2;"13")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>=INVERTBITS(A3;"13")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>11111111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>01010101</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>=INVERTBITS(A4;"1357")</TD></TR></TBODY></TABLE>

Note:

The UDF works under the basic assumption that the Byte that has bits to be changed is stored as Text rather than a number and returns a Textvalue.

It will invert any length of strings.