Say I have a string in the field A1 which is: "table 1". On another sheet is a table named "table_1".
Now I wanted to subsitute the space in "table 1" for a underscore.
I can do that with the Substitute function but I am not able to reference the table with the output of the SUBSTITUTE function:
=SUBSTITUTE(A1," ","__")
This will only return the string but Excel does not interpret this as the table name.
Putting this through INDIRECT or chaining a TEXT function did not work either:
=INDIRECT(SUBSTITUTE(A1," ","_"))
I want to do this in order to display some matrices(formatted as tables) from a separate sheet in my main sheet.
Thanks!
Substitute
? There may be a space before or after the string. Also, your first example has two underscores ("__
") and not one ("_
")