Hi,
I have a lot of sentence, Could you please help me to replace the last "," in below sentence by "and" and add "." at the end.
Input:
Aluminum Electrolytic Capacitors, Ceramic Capacitors, EMI Filters, Film Capacitors, Tantalum Capacitors, Supercapacitors, Flex Suppressor Sheets, Metal Composite Inductors, Miniature Signal Relays, EMI Cores for Cables, AC Line Filters
Output:
Aluminum Electrolytic Capacitors, Ceramic Capacitors, EMI Filters, Film Capacitors, Tantalum Capacitors, Supercapacitors, Flex Suppressor Sheets, Metal Composite Inductors, Miniature Signal Relays, EMI Cores for Cables and AC Line Filters.
Attached file contain the sample.
Thanks;
Bill
Hi Bill,
No VBA required for this, see attached workbook.
=SUBSTITUTE(A1,","," and",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))
By using SUBSTITUTE to remove all commas I am shortening the length of the string by the number of commas in it. Knowing the number of commas in the string I can then use SUBSTITUTE again to replace the last one with "and ".
You can use SUBSTITUTE to replace all occurrences of a substring, or a specific instance, and the above formula uses both approaches.
More on SUBSTITUTE
https://www.myonlinetraininghub.com/excel-substitute-formula
https://www.myonlinetraininghub.com/using-substitute-function-find-count-text
Regards
Phil
Thank you very much; your support is greatly appreciated.
Thanks;
Bill
