ExcelBanter - 'Substitute' function with wildcards

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - 'Substitute' function with wildcards (https://www.excelbanter.com/excel-discussion-misc-queries/237692-substitute-function-wildcards.html)

[email protected]

'Substitute' function with wildcards

Can you use wildcards with the substitute function? This is the
formula I am using.

=SUBSTITUTE(F51,"FY20??","FY2008")

However this doesn't work. It seems that the function is looking for
exactly 'FY20??' and not what matches FY20??. Can anyone help?

jasontferrell

'Substitute' function with wildcards

I don't think wildcards work in substitute. You could instead use
something like this for a one instance replacement:
=LEFT(A1,FIND("FY20",A1)-1)&"FY2008"&MID(A1,FIND("FY20",A1)+6,999)

All times are GMT +1. The time now is 10:33 PM.