Excel - Modify a string of characters

December 2016



Issue


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

Solution


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.

Related :

This document entitled « Excel - Modify a string of characters » from CCM (ccm.net) 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.