Text spacing with characters

[Str|ker]

Im trying to work out how to add spaces and characters entered by a barcode scanner to make it easier to read.

This is how it automatically inserts into a cell Z00126062021A10061101
This is how I would like it to look Z001-26062021-A1-006-1101


Sometimes the last set of values are 3 digits sometimes 4 see below.
Z001-26062021-A1-006-1101
Z001-26062021-A1-006-980


Can I use a formula to achieve this or is the only way using VB?

Thanks

[Phuocam]

Try this:

=LEFT(A2,4)&"-"&MID(A2,5,8)&"-"&MID(A2,13,2)&"-"&MID(A2,15,3)&"-"&MID(A2,18,4)

[Rick Rothstein]

Another way...

=REPLACE( REPLACE( REPLACE( REPLACE(A1,18,0,"-"),15,0,"-"),13,0,"-"),5,0,"-")

NOTE: You can remove the spaces between the REPLACE function calls if you want... they are there to stop this forum's comment processor from issuing an error message.