Need VBA to remove leading apostrophe in the text of a formula to convert the text to the actual formula

[jerry12302]

I build formulas using concatenation and range value them to create the text of the actual formulas I need in order to build complex link formulas with variable ranges and file names. Below is an example:

'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S22
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S23
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S24
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S21
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S22
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S23
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S24

Since Excel cannot link to another file using text of the desired file name and range within another cell, like I can do in Lotus with @@(@coord(3,5,100,8)), for example, I would like to remove the preceding apostrophes in the text of the linking formula using VBA. Currently I would have to edit every formula to manually remove it to get the formula I need. I have hundreds of these.

In Lotus a simple macro would be {EDIT}{HOME}{DEL}~ I could never find an Excel equivalent.

Any ideas?

 

[Micron]

Then the only way to get rid of the text format character (single apostrophe) is to use ClearFormats as I mentioned above. I'm referring to the ' you would see in the formula bar at the beginning of the cell contents displayed there but do not see in the actual cell contents. Text to columns won't work because ' is not data, it's a formatting character - invisible, kind of like linefeed characters and such. That makes me wonder how you seem to be getting that character into your output (wherever you're using your data).

 

[jerry12302]

Try using :

Cells.Replace What:="'XY", Replacement:="", LookAt:=xlPart, _

instead of

Cells.Replace What:="XY", Replacement:="", LookAt:=xlPart, _

I tried that, it didn't work, but thanks.

 

[jerry12302]

Another alternative:

Sub Test()
'
    Dim Cel As Range
'
    For Each Cel In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Cel.Value = "=" & Mid(Cel.Value, 2)
    Next
End Sub

That worked Johnny, excellent suggestion, I never used Mid in VBA before. Thanks, this will save a lot of headache.

 

[jerry12302]

Then the only way to get rid of the text format character (single apostrophe) is to use ClearFormats as I mentioned above. I'm referring to the ' you would see in the formula bar at the beginning of the cell contents displayed there but do not see in the actual cell contents. Text to columns won't work because ' is not data, it's a formatting character - invisible, kind of like linefeed characters and such. That makes me wonder how you seem to be getting that character into your output (wherever you're using your data).

Thanks, but the clearformats code did not work either. Johnny's answer works great, though. Thanks for the suggestions.

 

[Micron]

Thanks, but the clearformats code did not work either.

Then I'd say it was not a formatting character - it was actual data.

 

[Snakehips]

@jerry12302 you could perhaps try like below

Sub Test()
Set MyRange = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
MyRange.Formula = MyRange.Text
End Sub

Unlike Johnny's code that should not return '=" for any blank cells.?