CODEWITHSUNDEEP
excelexcel-formulavba
D. Bute
D. Bute

Reputation: 1

Find only two decimals

I am very new to Excel, a beginner! I need a formula/macro to find 1-2 decimals in a column. The column contains job codes for all levels of the organization. It would look something like this:

xx.xxx
xx.xxx.xxx
xx.xxx.xxx.xxx
xx.xxx.xxx
xx.xxx.xxx.xxx
xx.xxx.xxx.xxx.xxx
xx.xxx.xxx.xxx.xxx.xxx
xx.xxx.xxx.xxx.xxx.xxx.xxx

I need to find only the job codes with one or two decimals, which specify a certain position in the organization, the end result looking like this:

xx.xxx
xx.xxx.xxx
xx.xxx.xxx

Does anyone have any ideas on how to do this? Thank you in advance!

Upvotes: 0

Views: 311

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

To Do it without a helper column:

=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/(LEN($A$2:$A$9)-LEN(SUBSTITUTE($A$2:$A$9,".",""))<=2),ROW(1:1))),"")

enter image description here

Upvotes: 2

Jordan
Jordan

Reputation: 4514

You could use a helper column to count the occurrence of . and then just filter this column:

=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

Upvotes: 2

Related Questions