Problem description &amp; analysis:
In the following table, value of cell A1 is made up of names of several people and their attendances in four days. For example, c is 1100 expressed in hexadecimal notation, meaning the corresponding person has attendance in the 1st day and the 2nd day and is absent in the 3rd day and the 4th day. 
<div class="hn-table">
<table>
<thead>
<tr>
<td>A</td><td>B</td></tr>
</thead>
<tbody>
<tr>
<td>1</td><td>alice,c,bob,7,clara,a,mike,9</td></tr>
<tr>
<td>2</td><td>2</td></tr>
</tbody>
</table>
</div>We need to find the number of people who has the attendance in the day input in A2. For example, three people are present in the 1st day and two people are present in the 3rd day.
Solution:
Use SPL XLL to type in the following formula:
<pre><code class="lang-plaintext">=spl("=theDay=shift(1,?2-4),?1.split@c().step(2,2).count(and(bits@h(~),theDay)&gt;0)",A1,A2)
</code></pre>
As shown in the picture below:
<img src="https://preview.redd.it/in-excel-parse-hexadecimal-numbers-and-make-queries-v0-0i67ono0qg8d1.png?width=969&amp;format=png&amp;auto=webp&amp;s=5ea6ba17c8dc78bf4e7a24b997d244380766f0cc" alt="r/esProc_Desktop - In Excel, Parse Hexadecimal Numbers And Make Queries " />
Explanation:
shift()function performs a shift operation on an integer. step(2,2) gets members at the even positions. bits@h parses a hexadecimal number. The and operator represents and "AND" operation with bits() function, and ~ represents the current member.
esProc Desktop is now FREE to download and try this solution by yourself: <a target="_blank" href="http://www.scudata.com/download-Desktop">http://www.scudata.com/download-Desktop</a>

**Problem description & analysis:**

In the following table, value of cell A1 is made up of names of several people and their attendances in four days. For example, c is 1100 expressed in hexadecimal notation, meaning the corresponding person has attendance in the 1st day and the 2nd day and is absent in the 3rd day and the 4th day. 

| A | B |
| --- | --- |
| 1 | alice,c,bob,7,clara,a,mike,9 |
| 2 | 2 |

We need to find the number of people who has the attendance in the day input in A2. For example, three people are present in the 1st day and two people are present in the 3rd day.

**Solution**:

Use **SPL XLL** to type in the following formula:

```plaintext
=spl("=theDay=shift(1,?2-4),?1.split@c().step(2,2).count(and(bits@h(~),theDay)>0)",A1,A2)
```

As shown in the picture below:

![r/esProc_Desktop - In Excel, Parse Hexadecimal Numbers And Make Queries ](https://preview.redd.it/in-excel-parse-hexadecimal-numbers-and-make-queries-v0-0i67ono0qg8d1.png?width=969&format=png&auto=webp&s=5ea6ba17c8dc78bf4e7a24b997d244380766f0cc align="left")

**Explanation**:

shift()function performs a shift operation on an integer. step(2,2) gets members at the even positions. bits@h parses a hexadecimal number. The and operator represents and "AND" operation with bits() function, and ~ represents the current member.

esProc Desktop is now FREE to download and try this solution by yourself: [http://www.scudata.com/download-Desktop](http://www.scudata.com/download-Desktop)