FIND CHARACTERS within a FORMULA

[Nastech]

hi, is it possible to find characters within a formula?
so far have portion of what looking for from different example, that "would"
work.

=TRIM(LEFT($AX$430,SEARCH(":",$AX$430,40)))

if describe correct: keep all left of semi-colon, start search at pos 40
for semi-colon.

am trying to find what line number is designated in a formula in the first
portion of an array. so this is the start. thanks.

 

[ShaneDevenshire]

Hi

1. Please tell us what you are looking at (the formula)
2. and what characters you want to find, what you're looking for
3. and what you want to do with them (it) when you find it.
4. are you look for a formula that will find something in a cell or are you
looking for a formula that will find something in a formula?

 

[Nastech]

hi, thanks, just realized should include formula. what work on can tend to
be to much to look at-no response, pardon.

been trying variations of TEXT / FIND..
I use a hyperlink to get around, this version searches down for the next
occurance of the - friendly name in a hyperlink. since a range, or array is
used, error pops up when moving lines around, the first part of the array
comes up with a different line number than what the formula currently
occupies, is an error.
my use will include.. unqoute: find row 430 in: $AX430:$AX$1879 =
ROW(430)

think should be able to include this in one hyperlink at top to find an
error, but will put formula in temp work column in each line if have to.

the formula trying to test is: (sorry

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AX430:$AX$1879,1,0))-ROW(OFFSET($AX430,1,0)),MATCH(TRUE,OFFSET($AX430:$AX$1879,1,0)="dn",0))),"","#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A430),"$",""),ROW(),"")&INDEX(ROW(OFFSET($AX430:$AX$1879,1,0)),MATCH(TRUE,OFFSET($AX430:$AX$1879,1,0)="dn",0))),$AA$3,0))),"dn")


1st portion attempt gets: #VALUE! for:

=TRIM(LEFT($AX$430,SEARCH(":",$AX$430,40)))



XXXXXXXXXXXXXXX

 

[Nastech]

and so, this does not seem to work either (2nd part).

=TRIM(RIGHT(SEARCH("$",TRIM(LEFT($AX$430,SEARCH(":",$AX$430,40))),30)))

 

[Ron Rosenfeld]

hi, is it possible to find characters within a formula?
so far have portion of what looking for from different example, that "would"
work.

=TRIM(LEFT($AX$430,SEARCH(":",$AX$430,40)))

if describe correct: keep all left of semi-colon, start search at pos 40
for semi-colon.

am trying to find what line number is designated in a formula in the first
portion of an array. so this is the start. thanks.

You will need to use a VBA UDF to convert the formula to text.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window. Then
Insert/Module and paste the code below into the window that opens.

The use this in some cell:

=SearchFormula(find_text,within_formula,[start])

====================================
Option Explicit
Option Compare Text 'make case insensitive
Function SearchFormula(find_text As String, _
search_within, Optional start As Long = 1) As Long

If VarType(search_within) = vbString Then
SearchFormula = InStr(start, search_within, find_text)
Else
SearchFormula = InStr(start, search_within.Formula, find_text)
End If

End Function
========================================
--ron

 

[Nastech]

how do I set up formula, not sure what to enter inside, for:
(error should be in 1 column, many formula's)

what do the 3 items represent?
=SearchFormula(find_text,within_formula,[start])


will give that a try.. study (on some of that not sure if which items are
variables I need to enter..)

closest had got, work without the equal sign anyways:
=LEFT(RIGHT(AX430,LEN(AX430)-38),3)

Is there a way to treat the cell as text, even though it has a formula in it?
otherwise, dropped your code in with other script.

not sure how to set up formula to find first instance of (1st portion of
range, ie: row number) for the long formula example had supplied, do I
designate a column somehow. sorry, guesse question is will it find a first
portion of a range, that formula is "NOT" in that row:

if formula in row 429, and formula has: $AX430:$AX$1909
then circular reference error, how to find it?

(NOTE: xl's circular reference indication at bottom of xl window directs to
a cell number, is the incorrect row number for my errors. have to search 1
row at a time). ok, not when induced, but other times error wanders when
delete formula from claimed error cell.

thanks.

hi, is it possible to find characters within a formula?
so far have portion of what looking for from different example, that "would"
work.

=TRIM(LEFT($AX$430,SEARCH(":",$AX$430,40)))

if describe correct: keep all left of semi-colon, start search at pos 40
for semi-colon.

am trying to find what line number is designated in a formula in the first
portion of an array. so this is the start. thanks.

You will need to use a VBA UDF to convert the formula to text.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window. Then
Insert/Module and paste the code below into the window that opens.

The use this in some cell:

=SearchFormula(find_text,within_formula,[start])

====================================
Option Explicit
Option Compare Text 'make case insensitive
Function SearchFormula(find_text As String, _
search_within, Optional start As Long = 1) As Long

If VarType(search_within) = vbString Then
SearchFormula = InStr(start, search_within, find_text)
Else
SearchFormula = InStr(start, search_within.Formula, find_text)
End If

End Function
========================================
--ron

 

[Ron Rosenfeld]

Responses are inline:

how do I set up formula, not sure what to enter inside, for:
(error should be in 1 column, many formula's)

I have no idea what you mean.

Which formula are you asking about.
As I wrote, the formula I gave you can be entered in any cell.
So far as "error" is concerned, again, I don't know what you mean. You
asked for a formula that would return the location of a CHARACTER within
another formula. What does "error" have to do with that? (If the character(s)
are not found, the function will return a zero. If you want it to return an
error in that instance, that can be done, but you need to tell me what error
you wish to have returned).

what do the 3 items represent?
=SearchFormula(find_text,within_formula,[start])

Sorry I thought it would be obvious in the context. I used the usual method of
displaying this that is used in the Microsoft documentation for formulas. What
I wrote is similar to the documentation for the SEARCH worksheet function so I
thought you would be familiar. In any event:

find_text is the text or character(s) you are searching for

within_formula is the cell containing the formula you are searching (or it can
be just any string)

[start] is an optional argument with the same meaning as that input in the
SEARCH worksheet function. Look at HELP for details.

will give that a try.. study (on some of that not sure if which items are
variables I need to enter..)

closest had got, work without the equal sign anyways:
=LEFT(RIGHT(AX430,LEN(AX430)-38),3)

Is there a way to treat the cell as text, even though it has a formula in it?

Only by using VBA.

That is what the UDF I gave you will do. And it will also execute the SEARCH
function similar to the worksheet SEARCH function.

If you only want to change the formula in the cell into text, and not include
the SEARCH functionality then just use this UDF (although this will be less
efficient to search for a character(s) within the formula):

==============================================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
============================

otherwise, dropped your code in with other script.

I don't know what that means, nor how it will affect the working of my UDF.

not sure how to set up formula to find first instance of (1st portion of
range, ie: row number) for the long formula example had supplied, do I
designate a column somehow. sorry, guesse question is will it find a first
portion of a range, that formula is "NOT" in that row:

if formula in row 429, and formula has: $AX430:$AX$1909
then circular reference error, how to find it?

Since the reference AX430:AX1909 does not include row 429, it is not clear from
what you write why you should be getting a circular reference error.

If you want to find if 430 is in your formula, you enter "430" would be
"text_to_find"

If you want to find the first set of numbers in a formula that represents a row
number, that is a very different question form wanting to find CHARACTERS
within a formula. The solution is also more complicated as it requires
differentiating numbers that are part of a cell address from numbers that
merely represent constants within a formula.

Please try to define exactly what you want to accomplish.
--ron