Databases
StackOverflow2013
Postsdbo
POSum of certain letters occurrences in alphanumeric string using Excel
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSum of certain letters occurrences in alphanumeric string using Excel
    primarykey
    data
    text
    <p>I have an array of alphanumeric data used in application testing, and for certain reasons I need to calculate a <strong>sum of occurrences of letters from "a" to "f"</strong> in each string (this will be used for further data processing):</p> <pre><code>02599caa0b600 --&gt; should be 4 489455f183c1fb49b --&gt; should be 5 678661081c1h 66410hd2f0kxd94f5bb 8a0339a4417 f6d9f967ts4af6e 886sf7asc3e85ec 03f1fhh3c3a2am e491b17638m60 1m8h2m07bhaa4tnhbc4 29ma900a80m96m65 ca6a75f505tsac8 956828db8ts7fd1d cf1d220a59a7851180e a8b7852xd9e7a9 b85963fbe30718db9976 39b8kx8f85abb1b6 0xxb3b648ab a8da75f730d45048 588h69d344 </code></pre> <p>This is what strings look like, their length is about 10-30 symbols, and I suppose to have about <strong>3-5k</strong> of them daily for processing. Assumptions and limitations:</p> <ol> <li>Case of letters does NOT matter (happily).</li> <li>The list of letters may change one day, but very much likely still remains a <strong>range</strong>, e.g. a-k, d-g, etc. - therefore solution should be as much flexible as possible.</li> <li>Any temporary calculations / ranges are not prohibited, but the shorter the better.</li> <li>I'd prefer pure Excel solution, but in case it's too complicated - VBA still an option. Nevertheless, complicated Excel formula is better than "2-lines-of-code" VBA - if the 1st works as expected.</li> </ol> <p>Things I've tried so far (as I noticed, that practice here is very much welcome):</p> <ul> <li>Searched through already answered questions, but found no Excel-based solutions for anything similar. Other languages / approaches are not an option (except VBA).</li> <li>The best thing I got on my own so far are nested <code>SUBSTITUTE</code> functions, but it's dirty and very straightforward. Assuming the range may change to c-x that'll be a nightmare.</li> <li>I'm not a newbie to Excel, but things like complicated array formulas are still hard nuts for me - alas but true...</li> </ul> <p>Anyway, I do not ask for "ready-to-go" "out-of-box" solution - I ask for help and right direction / approach for self-learning and further understanding of similar problems.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMartijn Pieters
    1. USKsenia
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSum of certain letters occurrences in alphanumeric string using Excel
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSum of certain letters occurrences in alphanumeric string using Excel
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSum of certain letters occurrences in alphanumeric string using Excel
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks a lot to everyone who contributed. You all guys are brilliant, I could not even expect such helpful and detailed answers. +100 to SO!)
      singulars
      1. POSum of certain letters occurrences in alphanumeric string using Excel
      1. USKsenia
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload