Inverting bits

I

iinlayo

New Member
Joined
Jul 19, 2010
Messages
20
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
iinlayo said:
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.
Click to expand...
Try this
=IF(SEARCH(0,A1,2)=2,REPLACE(A1,2,1,1),REPLACE(A1,2,1,0))
 
Upvote 0
what if i want to invert the 2nd bit, 4th bit and the 6th bit?
example :

10111010 = 11101110? thanks
 
Upvote 0
Try with the following UDF:

Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Simple Conditional Format not working.
Replies
6
Views
343
Peter_SSs
Peter_SSs
J
  • Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
202
jmk15315
J
V
  • Question
Request help in extracting mutiple number groups from specific character positions from a varying number size.
Replies
3
Views
116
Fluff
Fluff
J
  • Question
Creating a list of items from multiple tabs, into a single sheet.
Replies
0
Views
145
jmk15315
J
E
  • Question
Deleting Rows with Specific Word along the Same Column VBA
Replies
4
Views
277
excelnoob1991
E

Forum statistics

Threads
1,214,429
Messages
6,119,433
Members
448,897
Latest member
ksjohnson1970

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top