excel 2019 advanced filter format ="=Symbol append "

[jbesr1230]

I am using excel 2019 advanced filter on a set of security symbols. Symbols are from 1 to 4 characters. I need to proceed the symbol with ="= and append " after the symbol. I can do this manually but since I need to repeat the process often multiple times a day on many symbols I would like a formula I could put into the column to the right that would put the symbols in the proper form e.g. from PM to ="=PM" and the latter will resolve to =PM after exiting the cell. Is this possible?

 

[cooper645]

What about the following, assuming that the PM is in Cell A1;

="="&A1

forgive me if this isn't quite what you need, if its not please explain further.

 

[jbesr1230]

="="&A1

Close. How would I append a single " after &A1? I've tried &""" which does not work.


see Filter by using advanced criteria

"Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

=''= entry ''

 

[cooper645]

="="&A1&""""

 

[jbesr1230]

Hello,
This still not getting the needed form.
For example, Cell B29 has TD in it
I go to B29,
go into edit mode. put the cursor at the far left i.e. before the T
type ="=
move the cursor to the right of the D and type " and then hit return.
what is in the cell when the cursor is hovering on the cell is ="=TD"
when I move the cursor off cell B29 what I see is =TD in cell B29. The closing quote mark is not visible.

This is following the instructions from microsoft

see Filter by using advanced criteria

"Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression
in the appropriate cell in the criteria range:

=''= entry ''

Thank you.
JB

 

[cooper645]

I cannot seem to get what you require in the way of a formula but I have written 4 Macros that would work for your purpose;

*Always try macros on sample data / copy of the original work book first

Sub ReEvaluateMulti()
'Rewrites multiple selected cells contents with the appendages
Dim Rng As Range, c As Range
    Set Rng = Selection
        For Each c In Rng
            c.Value = "=" & """" & "=" & c.Value & """"
        Next c
End Sub


Sub ReEvaluateIndividual()
'Rewrites the current cell with the appendages
    ActiveCell.Value = "=" & """" & "=" & ActiveCell.Value & """"
End Sub


Sub FromCellToRightMulti()
'Select the cells down the column to the right and use the column -1 offset to write the appendages
Dim Rng As Range, c As Range
    Set Rng = Selection
        For Each c In Rng
            c.Value = "=" & """" & "=" & c.Offset(, -1).Value & """"
        Next c
End Sub


Sub FromCellToRightIndividual()
'Select the cell to the right and use the column -1 offset to write the appendages
    ActiveCell.Value = "=" & """" & "=" & ActiveCell.Offset(, -1).Value & """"
End Sub

 

[Fluff]

when I move the cursor off cell B29 what I see is =TD in cell B29. The closing quote mark is not visible.

That is normal behaviour & should not be a problem.