To seperate the plancodes and count the number

[mithelesh]

Hi all

Goodday !!

Please suggest how to code using macro of using functions .

i used , Text to columns option in Data tab in excel menu

i got the seperation of plancodes .

then how to count the number of plans as shown in excel sheet .

Regards
Mithelesh.S

 

[Deepak]

Pls check this!!

=IF(ISERR(FIND(CHAR(10),A1)),IF(LEN(A1)=LEN(SUBSTITUTE(A1,",","")),LEN(A1)-LEN(SUBSTITUTE(A1,"/","")),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))+1

 

[mithelesh]

I have another scenario

Incell A1 , i have data as below :

Incell A1

UXZAT072D
UXZAT072DC
UXZAT072DM
UXZAT072E
UXZAT072EC
UXZAT072EM

if I use the formula now i get the value as 1 .
Please see the attached sheet .

Thankyou

Mithelesh.S

 

[Faseeh]

This one also:

=IF(ISERROR(FIND(CHAR(10),A1)),1,SUM((MID(A1,ROW(A1:A999),1)=CHAR(10))*1)+1)

Press Ctrl Shift Enter to run.

 

[Faseeh]

What about data in A2? Do you have it any other shape?

 

[mithelesh]

Hi Faseeh

Used the same code, the count is 1 again .

should we drag the formual or we need to use CTRL+SHIFT+ENTER

Please suggest

Mithelesh.S

 

[Faseeh]

You need to press Ctrl+Shift Enter...

 

[Deepak]

I have another scenario

......
if I use the formula now i get the value as 1 .
Please see the attached sheet .

Thankyou

Mithelesh.S

this will work for all three cases.


=IF(ISERR(FIND(CHAR(10),A1)),IF(LEN(A1)=LEN(SUBSTITUTE(A1,",","")),LEN(A1)-LEN(SUBSTITUTE(A1,"/","")),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))+1

 

[mithelesh]

Hi all

Thanks you for the replies both of them are working well .

~Mithelesh.S