Counting multiple letters in cells

[allunderme]

Hi,

I have a large list of policy numbers with different letters held within (eg MT/1/PD/101271A, 07BV/17345057, BL/1/ /635663A, MALZ32741) all have different formats and some contain /'s. They have all been manually added so each line differs slightly. I have a list of letter prefixes i wish to identify (BV, MT, MALZ etc) there are 11 in total.

Does anyone know how i can count these letter formats from this list of policy numbers?

Im scratching my head so any help would be really appreciated.

Allun

 

[hiker95]

allunderme,

Im still unable to complete my task but i wont be beaten

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Aladin Akyurek]

allunderme,



Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

Would an answer to the question posted in #10 be insufficient?

 

[hiker95]

Aladin Akyurek,

Would an answer to the question posted in #10 be insufficient?

I would have liked to see examples of all 11 types of Codes.

 

[allunderme]

Hi

From these codes i would expect to see

MT - 1
BV - 1
BL - 1
MALZ - 1

Each policy number will only contain one of these 2 or 4 letter codes. There would be no duplication but there would be a number of cells with no code included. I would need to see a count of these also but can be easily caluclated if i have the sum of all coded numbers.

Regards

Allun

 

[Aladin Akyurek]

Given a sample like:
[TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl63, width: 118, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]MT/1/PD/101271A
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] 07BV/17345057
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] BL/1/ /635663A
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] MALZ32741
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

what is the expected outcome?

Hi

From these codes i would expect to see

MT - 1
BV - 1
BL - 1
MALZ - 1

Each policy number will only contain one of these 2 or 4 letter codes. There would be no duplication but there would be a number of cells with no code included. I would need to see a count of these also but can be easily caluclated if i have the sum of all coded numbers.

Regards

Allun

[TABLE="width: 275"]
<colgroup><col style="width: 131pt; mso-width-source: userset; mso-width-alt: 6229;" width="175"> <col style="width: 48pt;" span="3" width="64"> <tbody>[TR]
[TD="class: xl63, width: 175, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 175, bgcolor: transparent"]MT/1/PD/101271A[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]MT[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 175, bgcolor: transparent"]07BV/17345057[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]BV[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 175, bgcolor: transparent"]BL/1/ /635663A[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]BL[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 175, bgcolor: transparent"]MALZ32741[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]MALZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

D2, copied down:

=COUNTIF($A$2:$A$5,"*"&$C2&IF(LEN($C2)=2,"/*","*"))

Is this what you are after?

 

[allunderme]

This looks great and is working on the majority of cells but seems to be missing out cells with the letter prefix at the beginning. eg BV12125554 or MT5455548.

Can you explain what the formular you have kindly provided is doing and i might be able to tweak it a little.

Regards

Allun

 

[Aladin Akyurek]

This looks great and is working on the majority of cells but seems to be missing out cells with the letter prefix at the beginning. eg BV12125554 or MT5455548.

Can you explain what the formular you have kindly provided is doing and i might be able to tweak it a little.

Regards

Allun

Are you able to say without any hesitation that, for example, the following would not occur:

BV12125554MTO
BVA12125554

 

[allunderme]

correct the following BV12125554MTO would not occur.

There would never been a combination of two letter prefixes within the policy numbers

 

[Aladin Akyurek]

correct the following BV12125554MTO would not occur.

There would never been a combination of two letter prefixes within the policy numbers

In that case, the following would suffice:

D2, copied down:

=COUNTIF($A$2:$A$5,"*"&$C2&"*")

which were already suggested in this thread.

 

[allunderme]

Brillant - works this time.

Thank you for all your help from everyone.