Ask a question »

Excel - Modify a string of characters

May 2016


I have a table with a list of codes and I want to insert dash between letters and numbers:
  • AA-978-BZ instead of AA978BZ.


If code is in A1:
  • In B1:
    • =LEFT(A1,2)&"-"&LEFT(RIGHT(A1,NBCAR(A1)-2),NBCAR(A1)-4)&"-"&RIGHT(A1,2) 
  • For a variable ranging from 3 to 4-digit numeric field.
  • Simpler if we assume that it is a 3 digit value:
    • =LEFT(A1,2)&"-"&LEFT(RIGHT(A1,NBCAR(A1)-2),3)&"-"&RIGHT(A1,2) 

Thanks to Vaucluse for this tip.

For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Published by aakai1056. - Latest update by aquarelle
This document entitled « Excel - Modify a string of characters » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.