how to remove "= sign"

[omairhe]

Hi...looking for a formula to remove an "= sign" at the very beginning. hence if "=Jordan" then want to change it to "Jordan" in cell T3 only.

but wait, the resulting cell T3 "=Jordan" already has a formula inside it =(U3&V3&W3&X3&Y3)

How to enter formula for removing "= sign" and not disturbing the existing formula ....


Will appreciate help!!

 

[omairhe]

this is the code that works with the U3 formula =AutoFilter_Criteria(U60) in this code i alter the second last line to remove some of more signs other than = (like Header and semicolon)

Function AutoFilter_Criteria(Header As Range) As String

Dim strCri1 As String, strCri2 As String



Application.Volatile



With Header.Parent.AutoFilter

With .Filters(Header.Column - .Range.Column + 1)



If Not .On Then Exit Function



strCri1 = .Criteria1

If .Operator = xlAnd Then

strCri2 = " AND " & .Criteria2

ElseIf .Operator = xlOr Then

strCri2 = " OR " & .Criteria2

End If



End With

End With



AutoFilter_Criteria = strCri1 & strCri2

End Function

 

[omairhe]

the problem here is not with your formula's provided but with the Macro Above in conjunction with my "& or Concatenate Formula".
even without applying urs formula to remove = sign it still misses out the value after a space/blank and stops working..
meaning if the filtered word is more than 1 word with space in between then
the & or Concatenate formula doesn't work correctly with the U3 Cell....

this is getting complicated.... sorry guys and appreciated your work.

Thank you

 

[Peter_SSs]

I have to say I don't understand the problem. In U61:U65 I have:

Smith Bill
Jordan Michael
Jones Tom
Jordan Michael
Smith Bill

I have my autofilter heading row as row 60 and have filtered column U on "Jordan Michael".

I have the AutoFilter_Criteria UDF exactly as you posted.

In U3:U8 I have a selection of formulas, none of which cut off the second word from the AutoFilter value.

Perhaps you could indicate what I have to change in my set-up and/or functions or formulas to demonstrate the problem.

Excel Workbook
	T	U	V	W	X	Y
3		=Jordan Michael	abc	def	ghi	jkl
4		=Jordan Michaelabcdefghijkl
5		Jordan Michaelabcdefghijkl
6		Jordan Michaelabcdefghijkl
7		Jordan Michaelabcdefghijkl
8		Jordan Michaelabcdefghijkl
9
59
60	HT	HU	HV
62		Jordan Michael
64		Jordan Michael
66
Sheet1

 

[omairhe]

oh man now i finally know where the problem is.... the text gets real big for the cell to show so with only one space between (=Jordan Michael346'marriedmemphis) hides the other part below it.
when increased the cell width or height the entire word appears. so macro and formula are all fine only Cell width T3 was the problem....lol

thanks for the reply otherwise couldn't have done it...