0
Thanks

A few words of thanks would be greatly appreciated.

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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Modify a string of characters », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments