macro to edit part of a cell contents

[selfteaching]

I have a formula to return the cell address based on a cell

=CELL("address",INDEX(A5:H600,MATCH(T3,A5:A600,0),0))

The return shows as an absolute cell reference ($A$10)
I’m at a loss on how to have a macro delete the $’s (or change the formula to do it)
I’ve tried

Sub deletesymbol()

  Selection.Value = Selection.Value
  Application.SendKeys ("{F2}{HOME}")
  ……….

Which changes the formula to a value
Then F2 sets up to edit
And home moves the cursor to the first $
Then …I’m stuck
I want to remove the two $’s and put a space between the letter and number
The return will always be A, but the number can be any number of digits (Example: &A$300)
mike

[Dave]

Module code...

Public Function StripChars(InputStr As String) As String
Dim i As Integer, Nstr As String, Tstr As String
For i = 1 To Len(InputStr)
If (Asc(Mid(InputStr, i, 1)) >= 65 And Asc(Mid(InputStr, i, 1)) <= 90) Or _
  (Asc(Mid(InputStr, i, 1)) >= 97 And Asc(Mid(InputStr, i, 1)) <= 122) Then
Tstr = Tstr & Mid(InputStr, i, 1)
GoTo below
End If
If IsNumeric(Mid(InputStr, i, 1)) Then
Nstr = Nstr & Mid(InputStr, i, 1)
End If
below:
Next i
StripChars = Tstr & " " & Nstr
End Function

To operate when input string in A1 ...

=StripChars(A1)

HTH. Dave

[p45cal]

or change the formula to do it

=SUBSTITUTE(CELL("address",INDEX(A5:H600,MATCH(T3,A5:A600,0),0)),"$","")

[selfteaching]

Hi Dave,


Works perfectly, thank you

I’ll have the cell reference out of view Then have it copied to where I need it.
FYI: I am using advance filter to find stuff from a large range and want the cell reference to show me where in the range it is. The criteria can have multiple results
Mike

[selfteaching]

Hi p45cal,
I didn't see your reply till after i replied to Dave.
That also works for what i want, Thank you
with yours, I can use it with an IF statement and put it where its needed.

2 more learning tools.

Thanks to both

mike

[selfteaching]

p45cal,

With Dave's function, the Letter and the number have a space between them.
I wondering if your way can do that.
I tried to add a space between the " and $, first one side, then both, but it didn't work

mike

[p45cal]

=TRIM(SUBSTITUTE(CELL("address",INDEX(A5:H600,MATCH(T3,A5:A600,0),0)),"$"," "))

[selfteaching]

Thank you p45cal
so simple for you...so hard for me

have a couple on me

mike

and thanks again Dave

[Dave]

You are welcome. Thanks for posting your outcome. As an aside and the reason for my approach, your example was &A$300 which included the "&" making a formula approach difficult (for me). Anyways, glad U were able to resolve this. Dave.