Removing user-specific characters from data

Hello Everybody

I've been playing with this problem now for a little while and am getting nowhere fast so any help is very much appreciated!

I've uploaded an example spreadsheet of the problem I'm trying to solve - I'm trying to set up a model in Excel for cleaning data, which allows the user to enter into a column the characters that they want to remove from a cell, and the excel functions will then look to see if any of the given characters in this column are present in the data and remove them accordingly.

I know I could nest multiple SUBSTITUTE functions together but I think the problem with this is knowing how many characters the user may want to look for, ie: if I nest ten together but they want to search for 12 different characters it won't look for the last 2. It needs to allow for the flexibility of the user to enter as many different characters as they want to clean from their data

If it's any help at all I did see a while ago a way of doing this which if memory serves me correctly enough involved an array function with TRIM and possibly MID, but can I find this now? No...

I'd prefer not to go down the route of VBA as i need to keep this model relatively simple for use by others

Can anyone help please?

Thanks in advance
Kenny

I could only do it via vba code (though in doing it i've solved a problem that's been bugging me for months).

I'm a total amateur at these things so apologies for the inelegant solution. This counts through text entries down in B3 down (up to a 100) then through your rogue characters in E3 down to row 20 and allows for words up to 20 characters long.

Please Login or Register  to view this content.

I've done some basic testing - hope it works for you.