Search Formula

[Guest]

I have text data in Column A (from A1 to A300), I would like to search each
cell in column A in order to get the position number of the character *
and the result in B cells, I am trying with the following code, but I have
problems in coding the A column :

For i=1 to 300
Cells(i,2).Formula="=Search("*","A",i,1)"
Next

Thanks for your help

 

[Dave Peterson]

with activesheet
.range("b1:b300").formula = "=search(""~*"",a1,1)"
end with

The asterisk is a wildcard (represents any set of characters). So you have to
tell excel that you're really looking for that character.

The tilde (~) is the character that does this.

~* to find *
~? to find ?
~~ to find ~

 

[Dave Peterson]

You may want to hide the errors if there is not asterisk:

With ActiveSheet
.Range("b1:b300").Formula _
= "=IF(ISERROR(SEARCH(""~*"",A1)),"""",SEARCH(""~*"",A1))"
End With

 

[Guest]

Thanks again Dave.

--
Moises

You may want to hide the errors if there is not asterisk:

With ActiveSheet
.Range("b1:b300").Formula _
= "=IF(ISERROR(SEARCH(""~*"",A1)),"""",SEARCH(""~*"",A1))"
End With