Ask a question »

Excel - Modify a string of characters

September 2015


Excel - Modify a string of characters



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.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-modify-a-string-of-characters.pdf

See also

In the same category

Published by aakai1056. - Latest update by aquarelle
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.