Replace Around Wilcards

[smitpau]

Hi,

Wonder if someone maybe able to help with this say I have the following names below.

If I search BS??BD it will find the two current results but if I try to keep the 3rd and 4th characters as 01,02 but update the Test for Final using BS??Final it will then insert the ??

Is there a way to essentially leave the searched wildcard values as they are?

PS appreciate you could search Test and replace with Final but in this case I need the specific search to avoid finding other non-relevant cells (like columns BD).


Current
BS01BD
BS02BD

Updated
BS01Final
BS02Final


Hope I've explained it clearly.

Thanks for reading

 

[Fluff]

Can you please explain what you are trying to do & how?

 

[smitpau]

Basically I've been trying to batch change names in name manager using the name manager linked add-in linked below.

To do this I've had to delete the old names, and reimport them with the new names (had to delete the old names to stop them duplicating).

This means I have some cells still using the old names like above which I'd like to change to the new names.

The problem is it's tricky to be replace the right cells as they're are 16 companies used so BS01, BS02 etc as the prefix then BD for Bad Debt for example.

I would rather not search BS01BD then BS02BD as this will take quite a bit of time.

I'd prefer to search BS??BD then somehow replace it so it maintains the BSxx but changed the BD to Bad_Debt.

Problem is if I just searched BS it would give cells I'm not trying to change.

Hope that makes sense.

Free Name Manager Excel add-in

Name Manager, for over 20 years the industry standard tool for managing range names in Microsoft Excel. A free Excel add-in.

jkp-ads.com

 

[Fluff]

I know nothing about that add-in, so unable to offer any further help.

 

[smitpau]

It's not really the add-in that affects the question to be honest.

It's trying to search using wildcards but then keeping those wildcards as they are when replacing the cell, if that makes sense.

 

[smitpau]

This maybe what I'm looking for just for reference.

Except this would only works cell by cell instead of being able to search and replace a sheet/workbook but it's the same principle.

Wildcards in 'Replace With' Text (Microsoft Excel)

When doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, you cannot use them in the replacement text. This tip examines ways you can work around this limitation and use wildcards in your replacements. (Tips.Net)

excel.tips.net

 

[Fluff]

But how are you searching?, Cell formula, manually (Ctrl F), VBA?
Also what do you want to do once you have found the values?

 

[smitpau]

Yes using Ctrl F.

It would change the ending of the cell names so BS01/BS02/BS03 would stay as that but the BD would change to Bad Debt for example.

 

[Fluff]

Whilst you can use wildcards with find, you cannot use them with replace & keep the original values intact.
If you had not deleted the old names, then all you would have needed to do, is change the the name in name manager & the formulae would have changed as well.
Do you have a backup of you file, from before you deleted the names?

 

[smitpau]

Yeah thanks for confirming appreciate that is the case which is a shame.

It's because it's a bulk change of 500+ names so tried to find a quicker way.

It's okay I've just searched using all the prefixes BS01,02,03 etc.

Thanks for helping, sorry for asking a question there isn't currently a solution for!