Making character substitutions in cells?
Greetings all,
I have another spreadsheet challenge.
I have two columns: Team Number (numbers 1 - 20), Team Name. I need to concatenate the two columns to create a file name, according to a specific file naming convention.
Using this formula: =If($A2<10,concat(concat(c
The problem that I need to solve is for names with illegal characters. The easy way is to disallow special character, but I prefer to find a technical solution, if possible,
For example:
Team names, "More > One" converts to "EventDay06-08-More > One" but I need to automatically change this name to "EventDay06-08-More-One", etc.
Basically, I need to replace illegal characters with a substitute.
See sample sheet:
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875
How can I make automatic character substitutions?
Thank you
Thank you
I have another spreadsheet challenge.
I have two columns: Team Number (numbers 1 - 20), Team Name. I need to concatenate the two columns to create a file name, according to a specific file naming convention.
Using this formula: =If($A2<10,concat(concat(c
The problem that I need to solve is for names with illegal characters. The easy way is to disallow special character, but I prefer to find a technical solution, if possible,
For example:
Team names, "More > One" converts to "EventDay06-08-More > One" but I need to automatically change this name to "EventDay06-08-More-One", etc.
Basically, I need to replace illegal characters with a substitute.
See sample sheet:
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=964764875
How can I make automatic character substitutions?
Thank you
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Connelly,
In the sample sheet, i can see that you have all figure out in Google Sheets using Concat and RegexReplace functions?
Is your challenge then to do the same thing in Excel. If it is then the equivalent of Concat is Concatenate in Excel. As for RegexReplace, will need to write a macro function or will Substitute formual like what Saqid suggested. I would recommend writing macro as it is more manageable,
Let me know if I have interpreted your problem statement accurately.
In the sample sheet, i can see that you have all figure out in Google Sheets using Concat and RegexReplace functions?
Is your challenge then to do the same thing in Excel. If it is then the equivalent of Concat is Concatenate in Excel. As for RegexReplace, will need to write a macro function or will Substitute formual like what Saqid suggested. I would recommend writing macro as it is more manageable,
Let me know if I have interpreted your problem statement accurately.
ASKER
Thank you, everyone!
I combined Saqib Husain, Syed simplified formula ("EventDay06-"&text(A2,"00
I've learned so much in the past two weeks, thanks to the EE experts. :)
I combined Saqib Husain, Syed simplified formula ("EventDay06-"&text(A2,"00
I've learned so much in the past two weeks, thanks to the EE experts. :)
=substitute(substitute(sub