Formula

[JWR55]

I'm working on a spreadsheet and the first column is numbers that are Police
Case Files. These numbers should be year and 4 digit number (08-0585). The
numbers (text) after the date 08 have been entered incorrectly without the -
0. Is there a
formula I could use to change all the case files to add the -0.

 

[FSt1]

hi
try this....
=LEFT(B2,2)&"-"&RIGHT(B2,4)

coverts 080585(your example) to 08-0585

adjust cell reference to fit your data.

regards
FSt1

 

[JoelS]

hi
try this....
=LEFT(B2,2)&"-"&RIGHT(B2,4)

coverts 080585(your example) to 08-0585

adjust cell reference to fit your data.

regards
FSt1





- Show quoted text -

Was thinking this might better answer the question:
=LEFT(A2,2)&"-0"&RIGHT(A2,3)

 

[Pecoflyer]

Or use Ctrl+H

replace what: 08
with 08-0

Click Replace All

 

[Bryce]

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 0 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)