Contact KRS: +27 (0) 21 681 2900 | enquiries@krs.co.za | Contact Us

Musings

Excel: Search and Replace

29 Feb 2012 by Jasper Cook

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:

Search and Replace

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

Load more comments
Thank you for the comment! Your comment must be approved first
comment-avatar