ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Search Formula (https://www.excelbanter.com/excel-programming/391909-search-formula.html)

Moises

Search Formula
 
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
--
Moises

Dave Peterson

Search Formula
 
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 ~



Moises wrote:

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
--
Moises


--

Dave Peterson

Dave Peterson

Search Formula
 
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



Moises wrote:

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
--
Moises


--

Dave Peterson

Moises

Search Formula
 
Thanks again Dave.

--
Moises


"Dave Peterson" wrote:

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



Moises wrote:

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
--
Moises


--

Dave Peterson



All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com