Need macro to Count Number of Dots Before the Word Starts

[madhuchelliah]

Hello guys, In I column some of the cells starts with "." It might be start with two to six dots also i.e ".." to "......". After that word will start. Now i want to count number of dots before the word starts and add the count with value 1 the result should be reflect in corresponding cell in E column. see the example table below. Some times there will be space between the dots(". . . . ") my values starts from cell I2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]E
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Adam
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]..Smith
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]. . David
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]...John
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]. . . .Rob
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rick
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Matt
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD].Justin
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]. Miller
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, assuming, as per the sample data, the dots only appear at the start - you could try this formula:

=IF(ISNUMBER(FIND(".",I2)),1+LEN(I2)-LEN(SUBSTITUTE(I2,".","")),"")

If you really did need code you could use the formula in VBA like this.

With Range("E2:E" & Range("I" & Rows.Count).End(xlUp).Row)
  .FormulaR1C1 = "=IF(ISNUMBER(FIND(""."",RC[4])),1+LEN(RC[4])-LEN(SUBSTITUTE(RC[4],""."","""")),"""")"
  .Value = .Value
End With

 

[madhuchelliah]

Hello Form, the code is working fine. But i want to count the dots before the 1st word only not all the dots in the cell.

 

[FormR]

It might be start with two to six dots

You could try this formula:

=IF(LEFT(I3)=".",MATCH(TRUE,INDEX(MID(SUBSTITUTE(I3," ",""),{1,2,3,4,5,6,7},1)<>".",0),0),"")

Or in code:

With Range("E2:E" & Range("I" & Rows.Count).End(xlUp).Row)
  .FormulaR1C1 = "=IF(LEFT(RC[4])=""."",MATCH(TRUE,INDEX(MID(SUBSTITUTE(RC[4],"" "",""""),{1,2,3,4,5,6,7},1)<>""."",0),0),"""")"
  .Value = .Value
End With

 

[madhuchelliah]

Thank you Form. Working Great !!!!!!