File Format Change

CozNoleon

New Member
Joined
Feb 9, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a schedule that has links to multiple files which are in .xls format.

However in their wisdom the person who created the files decided to change to format to .xlsb this year. The rest of the filename has stayed the same.

Is there a way I can quickly change the extension in the formula in my sheet so that they now point to the .xlsb version?

I've tried using file - replace but that's not working as it's prompting me to Update Values. There are 1000's of links to 24 different workbooks so the thoughts of having to manually rebuild my schedule is turning my stomach as it's something that's evolved over years. To start from scratch would be a total nightmare.

Below is a redacted sample of how the old links work
='N:\Folder\subfolder\[Filename.xls]Tab1'!$W8

And this is what I need to change them to
='N:\Folder\subfolder\[Filename.xlsb]Tab1'!$W8
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Ron de Bruin

Active Member
Joined
Aug 1, 2006
Messages
271
Platform
  1. Windows
  2. MacOS
Select the formula cells

Ctrl F
Select the Replace tab
xls in the find what
xlsb in the Replace with

Click on Replace all
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,992
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You should just be able to do a Find/Replace. Just make sure that the Lookin is set to formulas and the Match entire cell contents is not checked.
xls in the Find box and xlsb in the Replace with
 

Watch MrExcel Video

Forum statistics

Threads
1,127,806
Messages
5,627,005
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top