Counting letters and numbers separately in a single cell

**ss2012** · 08-22-2019, 02:39 AM

I would like to create a VBA script/module for Microsoft Excel to do the following.

Identify if the cells in said column meet the two sequences below and highlight the cells Red that do not meet the sequence and highlight the cells green that do meet the sequence.

3 letters 3 numbers (AAA111)
1 number 2 letters 1 number 2 letters (1AA2BB)

With the sequences above they might be separated by a comma and in different orders, example.

AAA111
1AA2BB
AAA111,1AA2BB

Please see below examples I gathered from around the internet. However it does not allow for commas and the first sequence 3 letters 3 numbers (AAA111).

Please Login or Register  to view this content.

**Rick Rothstein** · 08-22-2019, 03:04 AM

I think this macro might do what you want...

Please Login or Register  to view this content.

Note: It wasn't clear to me what column your data was in, so I guessed at Column F. If that was a wrong guess, then change the red F's to the correct column letter designation.

**ss2012** · 08-22-2019, 03:42 AM

Thank you it works perfect!

Is it possible to ignore case sensitivity? and if a blank entry make it highlight red as well?

**Rick Rothstein** · 08-22-2019, 03:50 AM

Originally Posted by ss2012

Is it possible to ignore case sensitivity? and if a blank entry make it highlight red as well?

Give this macro a try...

Please Login or Register  to view this content.

**Sintek** · 08-22-2019, 04:47 AM

Out of interest...Trying to figure out Regular expressions...

Please Login or Register  to view this content.

**ss2012** · 08-22-2019, 07:46 AM

Originally Posted by Rick Rothstein

Give this macro a try...

Please Login or Register  to view this content.

Thank you

Originally Posted by sintek

Out of interest...Trying to figure out Regular expressions...

Please Login or Register  to view this content.

Complie error:
User-defined type not defined

**Sintek** · 08-22-2019, 07:51 AM

Need to enable...
Untitled.png

**ss2012** · 08-22-2019, 10:17 PM

Hey, just came across some anomalies it seems if it has a 0 it doesn't the recognise the sequences

Rick Rothstein see example that is highlighted green:
20U8KK
50G20E

sintek see example that is highlighted green:
0LQ123
60F30Q

**jindon** · 08-22-2019, 10:25 PM

Can you just upload a small sample workbook with data and the result that you want?

**jindon** · 08-22-2019, 10:54 PM

Perhaps

Please Login or Register  to view this content.

**Rick Rothstein** · 08-22-2019, 11:04 PM

Originally Posted by ss2012

Hey, just came across some anomalies it seems if it has a 0 it doesn't the recognise the sequences

Rick Rothstein see example that is highlighted green:
20U8KK
50G20E

The pattern for the above are ##A#AA and ##A##A (where # means a digit and A means a letter). You said the "green" patterns were AAA### and #AA#AA. Since neither of your above examples meets the "green" patterns, I colored them red. Why was that wrong to do?

**ss2012** · 08-22-2019, 11:44 PM

The correct sequence should be AAA### and #AA#AA

However with #AA#AA if a 0 is in any of the A's it will highlight green but should be red because its not a Letter its a Digit. I hope that makes sense.

Originally Posted by jindon

Perhaps

Please Login or Register  to view this content.

I'll try this shortly.

**Rick Rothstein** · 08-22-2019, 11:50 PM

It highlights red... not green... for me. Are you sure you have zeros where you indicate and not the letter O in upper case? Also check to make sure a trailing blank space (ASCII 32) or trailing non-breaking space (ASCII 160) did not accidentally get into the cell.

**Sintek** · 08-23-2019, 12:05 AM

sintek see example that is highlighted green:
0LQ123
60F30Q

These and any other anomalies are highlighted red...
As per jindon's suggestion...Upload a sample file with depicted expected results...

**ss2012** · 08-26-2019, 07:41 PM

Thank you for the help I will be able to get the example document uploaded later tonight.

Below is another example that shows green for me but the second entry should make it highlight red because of the 0 is not a A-za-z

Please Login or Register  to view this content.

**ss2012** · 08-27-2019, 04:17 AM

I am unable to attach a document due to not enough posts.

Cell A1 should be Red
Cell A5 should be Red
Cell A7 should be Red
Cell A8 should be Red

Please paste the following in cell A1

Please Login or Register  to view this content.

**AliGW** · 08-27-2019, 04:20 AM

You can attach a workbook with just ONE post!!!

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**jindon** · 08-27-2019, 04:44 AM

Please Login or Register  to view this content.

**ss2012** · 08-27-2019, 08:39 AM

Originally Posted by jindon

Please Login or Register  to view this content.

Awesome! are you able to explain the regex?

**jindon** · 08-27-2019, 08:45 AM

Google with the keywords like

VBA Regular Expression

And you will get many web sites like https://analystcave.com/excel-regex-tutorial/

Reg, your problem.

You gave us the wrong conditions, number 0 Is NOT capital O...

**ss2012** · 08-27-2019, 06:22 PM

thanks will suss it out.

Also are you able to re-add as I am just having a bit of trouble incorporating it.

Please Login or Register  to view this content.

Counting letters and numbers separately in a single cell

LinkBack

Thread Tools

Rate This Thread

Display

Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Re: Counting letters and numbers separately in a single cell

Thread Information

Users Browsing this Thread

Similar Threads

Incrementing Letter / Numbers / Letters in a single cell

[SOLVED] arrange text and numbers separately in a single cell

Selecting letters/characters/numbers separately for a filter

[SOLVED] i have a data with numbers with letters in single cell , i need to split numbers and names

[SOLVED] counting whole and decimal numbers separately

[SOLVED] Counting Numbers within a Single Cell

Counting Letters & numbers

Bookmarks

Bookmarks

Posting Permissions