Convert With Dashes

[Billy Spivy]

Hello all,

Is there a way to convert this data from before to after applying dashes where lacking?

Before After
P-008037 P-008037
P26903 P-26903
P77897 P-77897
P2070 P-2070
P2048 P-2048
P4551 P-4551
W2059:001 W-2059:001
W2044:001 W-2044:001
W1169:001 W-1169:001
W1779:001 W-1779:001
P-1969 P-1969
P-4623 P-4623
P4768 P-4768
P4084 P-4084

Thank you for any help

[modytrane]

see attached.

[sandy666]

You can try with PowerQuery add-in for Ex2010

[Phuocam]

Try this ...

=IF(MID(A1,2,1)="-",A1,REPLACE(A1,2,0,"-"))

[63falcondude]

Another option just for the fun of it

=IF(COUNTIF(A2,"?-*"),A2,LEFT(A2)&"-"&RIGHT(A2,LEN(A2)-1))

[Billy Spivy]

My before data is in column E starting at row 2. How can the mid formula be adjusted please?

[Billy Spivy]

I got it thanks so much.

=IF(MID(E2,2,1)="-",E2,REPLACE(E2,2,0,"-"))

Is there a way to force the user to this format?

P- or W-

[63falcondude]

Is there a way to force the user to this format?

P- or W-

You can use Data Validation. If your data is in E2:E10, highlight E2:E10 > Data Validation > Settings > Allow: Custom > Formula: =COUNTIF(E2,"?-*") > OK

If you only wanted to allow for P or W, you can adjust the formula to this: =COUNTIF(E2,"P-*")+COUNTIF(E2,"W-*")

[Billy Spivy]

Great thank you for all of the help.

[63falcondude]

Glad we could help.