Thread: Formula
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Bryce Bryce is offline
external usenet poster
 
Posts: 24
Default Formula

If only some entries are incorrect, you could use a combination of what has
been suggested:

=IF(LEN(A1)=6,LEFT(A1,3)&"0"&RIGHT(A1,3),A1)

Apparently I need practice explaining formulas, let me know how I do:

If the string length of cell A1 is only 6 (eg 08-885) instead of 7 (eg
08-0885) then combine the left 3 characters of A1 (eg 08-) with a 0 and the
right 3 characters (eg 885), otherwise copy cell A1 (eg if it has all 7
characters, just copy it as is)