Find and Replace

[Niraj Baraili]

Hi All,

Need your help on this .......

Sample file has been attached. In column A, I need to replace first break with break 1 and second one with break 2. Googled a lot but could not able to figure out the solution.

 

[Hui]

B2: =IF(A2="Break",MAX($B$1:B1)+1,"")
C2: =IF(ISNUMBER(B2),"Break "&B2,IF(A2="","",A2))
Copy both cells down to row 6903
Then select Column C and Copy, Paste Special Values ontop of itself
Then replace Column A with Column C
Delete Column B

See attached file:

 

[Niraj Baraili]

Thank you Hui.

I need the result in different way ....for first break it should be break 1 and second it has to be Break 2. Similarly for, Break 3 and Break 4 it has to be Break 1 and Break 2........

 

[NARAYANK991]

Hi Niraj ,

For counting the breaks , try this :

=IF(A2="Break",IF(ISODD(COUNTIF(A$2:A2,"Break")),"Break 1","Break 2"),IF(A2 = "", "", A2))

Narayan

 

[Hui]

Change B2: to =IF(A2="Break",IF(ISODD(COUNTIF($A$1:A2,"Break")),1,2),"")
Copy down

 

[Niraj Baraili]

Excellent.
Thank you Guys.