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]

Research vba Replace function. I don't know what is on the left side of that = sign so I didn't proffer a suggestion.

 

[Alex Blakenburg]

Simply using Data > Text to Columns > Delimited (TAB) should work.

 

[jerry12302]

Research vba Replace function. I don't know what is on the left side of that = sign so I didn't proffer a suggestion.

It is a single quote, which precedes any left justified text entry in a cell, nothing unusual.

I researched various examples of the Replace code in VBA, and when I try it it always results in a string, never removes the quote, even if I precede the = with 'XY, like one user was doing:

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

Cells.Replace What:="XY", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

He said that processed worked for him, but I end up with the same string, but with the XY removed. Single quote is still there.

 

[Micron]

Sorry, misunderstood. Thought you were dealing with a literal character. Did you try Range(YourRangeHere).Clearformats ?
Seems to work for me.

 

[Sanjeev1976]

It is a single quote, which precedes any left justified text entry in a cell, nothing unusual.

I researched various examples of the Replace code in VBA, and when I try it it always results in a string, never removes the quote, even if I precede the = with 'XY, like one user was doing:

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

Cells.Replace What:="XY", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

He said that processed worked for him, but I end up with the same string, but with the XY removed. Single quote is still there.

Try using :

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

instead of

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

 

[johnnyL]

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

 

[Micron]

Example(s) of desired output are always a good idea as opposed to describing. I can't tell if you want to remove ' at the beginning:
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
becomes
=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21

or
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
becomes
'=+C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report!S21
or what.

AFAIK, using sheet function Replace isn't the same as vba function Replace. VBA function as
replace("=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S24","'","")
will give
=+C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report!S24

but it won't remove the formatting character ( ' ) at the beginning.

 

[jerry12302]

Example(s) of desired output are always a good idea as opposed to describing. I can't tell if you want to remove ' at the beginning:
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
becomes
=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21

or
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
becomes
'=+C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report!S21
or what.

AFAIK, using sheet function Replace isn't the same as vba function Replace. VBA function as
replace("=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S24","'","")
will give
=+C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report!S24

but it won't remove the formatting character ( ' ) at the beginning.

Your first example is my desired outcome. Good suggestion, I will start to also include my desired outcome.

 

[jerry12302]

Simply using Data > Text to Columns > Delimited (TAB) should work.

That did not do anything, but thanks.