Excel Help Thread ๐Ÿ“Š

Just wanna say I love macros

1 Like

Absolutely amazing! That has worked perfectly. Thanks for taking the time to look at that.


Such a dumbass thing to want to do with a spreadsheet anyway!


would love it if it was a test and they wanted you to just use access instead :smiley:


Another query from me!

Iโ€™m looking to save a workbook to a location based on tbe value of a cell on another workbook.

I have the below code:

ActiveWorkbook.SaveAs Filename:=โ€œC:\Temp\Exercise Output\Feb Order - Customer โ€œ & Cells (2, 2) .Value

Itโ€™s the value in cell 2, 2 I want to have in the file name but how do I write into the code that itโ€™s cell 2, 2 on sheet 2?

Any help is much appreciated.

Might be easiest to copy across to hidden sheet on active workbook first?

Do you really mean itโ€™s on another workbook and not just another worksheet? And, if so, which workbook is the code running in? The one with the location info or not?

SFileName = workbooks(โ€œwhatever.xlsxโ€).worksheets(โ€œsheet2โ€).Range(โ€œB2โ€).text


sFilename = Range(โ€œ[whatever.xlsx]Sheet2!B2โ€).value

Google Apps Script query

Iโ€™ve got a sheet with a script assigned to a button. Itโ€™s been working fine since I created it last year and hasnโ€™t been changed at all

Itโ€™s now producing a โ€˜script function could not be foundโ€™ error message

Any ideas why it might now be doing that?

I tried making a copy of the document and it worked in that
I tried renaming the script and reassigning it and that didnโ€™t work
Iโ€™m not at all confident with Google Apps Script and googling hasnโ€™t produced anything helpful

Hey Excel geniuses, does anyone know if itโ€™s possible to create a formula to check if a cell contains any characters that are NOT in a defined range?

How big is the range of characters? Can you post an example?
I can think of a fairly long-winded way of doing it if itโ€™s half a dozen characters but it wouldnโ€™t be very dynamic or scaleable.

Doing it the other way round (testing if all or none of the characters in the defined range are in a value) is annoyingly quite straightforward.

1 Like

What do you mean by defined range?

I think excel can cope with ascii which might help

probably something involving substitute, but definitely need a bit more info

Yeah, this was my idea. If itโ€™s five or ten possible characters then you can just nest the SUBSTITUTE calls, but I couldnโ€™t think of a way to do it array style.

Hereโ€™s an example. If you have a list of characters in range A1:A5, this will return how many characters in the value in C6 are not in that list.


Alternatively just add helper formulae to count the instance of each unwanted character, then subtract from the length of the entire string.

Itโ€™s not pretty, but it works.

Thanks guys, so I have about 85 characters I need to look up against, I can do this as values in a single cell or in a list, but itโ€™s basically a list of โ€˜safeโ€™ characters to be used in URLs

Then I need to look up whether a list (around 3k in this instance) of URLs contains any characters outside of this set of characters

So my hypothetical thing would be as follows:

So my hypothetical thing would be as follows:

Country Overall Length A C Excl. Length
Afghanistan =LEN(A2) =$B2-LEN(SUBSTITUTE(UPPER($A2),C$1,)) =$B2-LEN(SUBSTITUTE(UPPER($A2),D$1,)) =B2-SUM(C2:D2)
Albania 7 3 0 4
Algeria 7 2 0 5

What I like about it is that you can essentially paste the same formula (i.e. the โ€œ=$B2-LEN(โ€ฆโ€ one) into as many columns as you like, as long as you have the forbidden/allowed character there. Then the last column is just a way of checking whether or not it appears, and can be tweaked quite easily, I think.

None of these functions should be too time-consuming either


So my hypothetical thing would be as follows:

OK, this looks interesting, Iโ€™m going to have a play around with it, cheers!