Need Formulas

[CARBOB]

https://app.box.com/s/s7qk7yds4q1gdwmhl4du9zccr4n27znh



Col G formula to place digits in col B low to high
Col D formula to place OE count in col D
Col F formula to place LH count in col F

Thanks for all the suggestions!

 

[kweaver]

Counting the appearance of text "OE" in column C could be done with:

=(LEN(C2)-LEN(SUBSTITUTE(C2,"OE","")))/2

Similarly, counting the text "LH" in column E with:

=(LEN(E2)-LEN(SUBSTITUTE(E2,"LH","")))/2

and fill both down as needed.

 

[CARBOB]

Counting the appearance of text "OE" in column C could be done with:

=(LEN(C2)-LEN(SUBSTITUTE(C2,"OE","")))/2

Similarly, counting the text "LH" in column E with:

=(LEN(E2)-LEN(SUBSTITUTE(E2,"LH","")))/2

and fill both down as needed.

Returns only a 2 not the 3/2

 

[kweaver]

What's the meaning of the 3? for OOEOE ? I assume 2 is the number of OE's.

If not, you'll have to explain the meaning of the digits in 3/2, etc. because your original statement was count the OEs.

 

[kweaver]

AH, maybe you meant count the Os and count the Es???

=LEN(C2)-LEN(SUBSTITUTE(C2,"O",""))&"/"&LEN(C2)-LEN(SUBSTITUTE(C2,"E",""))

and

=LEN(E2)-LEN(SUBSTITUTE(E2,"H",""))&"/"&LEN(E2)-LEN(SUBSTITUTE(E2,"L",""))

BTW: are the numbers in column B ALWAYS 5-digit numbers??

If they're always 5-digits, I have ONE method, but it requires some helper columns.

W2 filled down: =TEXT(B2,"00000")
X2 filled down and across to AB: =1*(MID($W2,COLUMN()-(COLUMN($X2)- 1),1))
AC2 filled down and across to AG: =SMALL($X2:$AB2,COLUMN()-28)
And, finally AH2 filled down: =TEXTJOIN("",,AC2:AG2)

OR, you can adjust to another cell range (e.g., column G) and use my AH formula above in that range.

 

[CARBOB]

Very well done thank you very much!!!