Spaces are disallowed in Excel named ranges. If someone, say “Fred Nerx”, wants to name a range after himself, and then look up values in that range, there’s a problem. Fred will find that the Name Manager inserts an underscore into his name. He has now become “Fred_Nerx”. However, his boss doesn’t want it displayed like that in a report. He wants:
Fine. Just replace the space in his name with an underscore, then look up in the range of that name. The Excel REPLACE function needs the following arguments:
REPLACE(old_text, start_num, num_chars, new_text)
To replace a space (old_text), we know we want to replace only 1 character (num_chars), and we know to replace with an underscore (new_text), but names differ! How do we find the position (start_num) of a space in any name[1]?
Use FIND or SEARCH to supply a character position where a space is found:
=COUNTIF(INDIRECT(REPLACE($A2,SEARCH(” “,$A2),1,”_”)),B$1)
In the example formula, we pass the range name “Fred_Nerx” to INDIRECT as a string. The space in A2 is replaced by an underscore, and we use COUNTIF to find a count of the items specified by the headers in Row 1 (“Delay” in B$1, “Cancel” in C$1, etc).
[1] It gets complicated with names like “van der Merwe”, with multiple spaces, I know …
Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS and Jasper can assist you with your Excel: enquiries@krs.co.dev