microsoft excelmicrosoft-excel-2007
In one column I have a bitstring encoded as a string type. In a different column I wish to have the same bits (potentially starting with a leading 0), but as a binary value type. Ultimately, I want to have it in hex representation (string or hex type, doesn't matter) in a third column.
How is this done? Do I have to program something for it or is there a faster way?
Input cell contains the following string: 00000100100011000100100011
I wish to obtain the string: 0123123
I don't think there is any easy way to do this with existing Excel formulas. The problem is two-fold. First, AFAIK there is no Excel function that will tell you if a cell is part of a merged range. Second, the value shown in a merged ragne is actually only in the first cell of the merged range.
If you are willing to use VBA to create a custom function, this can be done fairly easily with a combination of the Match function and the fact that Range objects know if they are part of a merged range.
Public Function VLookupMerge(lookup_value As Variant, table_array As Range, col_index As Long) As Variant
Dim sMatchFormula As String
Dim row_index As Variant
Dim r As Range
sMatchFormula = "=MATCH(" & lookup_value _
& "," & table_array.Columns(1).Address(External:=True) _
& ",0)"
row_index = Application.Evaluate(sMatchFormula)
If TypeName(row_index) = "Error" Then
VLookupMerge = row_index
Else
Set r = table_array.Cells(row_index, col_index)
VLookupMerge = r.MergeArea.Range("A1")
Set r = Nothing
End If
End Function
@fixer1234 is right —
COUNTIF counts the cells that are equal to a value,
not cells that contain a string.
For that, you need to use FIND or SEARCH.
(They are identical, except FIND is case-sensitive
and SEARCH is case-insensitive.
I’ll just assume that you want the case-insensitive one.)
Start by doing
=SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)
This will look for the value of E2 (in your example, “ animal ”)
in cell B1 of the other worksheet.
If that string value is present in that cell,
this will return the location of
the first occurrence of the search string in the cell’s text
(with the first character being 1).
If the string is not present, it will return #VALUE!.
Next, do
=IF(ISERROR(SEARCH(E$2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)), 0, 1)
This will evaluate to 1 if the string is present and 0 if it is not. The next step is:
=SUM(IF(ISERROR(SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))
This sums the previous formula along column B of the other worksheet,
giving you the count that you want.
Note that the above is an array formula.
This means that, to get it to work,
you must type Ctrl+Shift+Enter
after you type the formula.
Now you can put this into cell M2 and drag down.
You don’t really need to have column E —
you can handle it within your SEARCH formula:
=SUM(IF(ISERROR(SEARCH(" "&C2&" ", '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))
I tested this in Excel 2013, but I’ve done things like this before, and I expect that this solution will work in Excel 2007. (And I tested with cells with more than 750 characters, and with a workbook file name that contains a space.)
P.S. I don’t know why you got those #VALUE! errors
in the “Function Arguments” dialog; it worked for me:
(I tested it even though my answer doesn’t use COUNTIF.)
Do you have the other workbook open while you’re doing this?
Best Answer
Just split the binary number with mid(cell, index_start, len) and do a piecewise change of base with bin2hex() followed by a concatenation (via CONCATENATE() - cell references are delimited by ampersands).
Example row:
0010000100000001110100101 is in one cell X1
Split it up into ceil(len(X1)/8)=4 cells to get groups of 8 bits each.
To split it into the 4 cells, use =MID($X1,start_pos,8), where startpos is the starting index (1 based) of the bitstring in X1
In another set of 4 cells concert the previous 4 cells into hex by referencing them with =BIN2HEX(8bitNrCell,2)
Concatenate the previous 4 cells with =CONCATENATE(1stcell&2ndcell&thirdcell&fourthcell)