Custom Cell format for hyphenated codes including letters and numbers

[will26]

Hi,

Im a novice user of excel so if this gets complicated i might struggle to follow.

im trying to populate an excel document with codes that look like this:

FTH-DSA-XX-ZZ-FF-A-0200
FTH-DSA-XX-XX-FF-A-0201
FTH-DSA-XX-01-FF-A-0202
FTH-DSA-XX-02-FF-A-0203

please note the overall length of the codes will be the same and the characters between the hyphens will also be the same and in the same position along the code.

I've tried to create a custom formula within the format cells dialogue box
but it only works with numbers using ###-###-##-##-##-##-#-####
how do i do this with both numbers and letters?

i suppose the goal is to be able to write
FTHDSAXX01FFA1200 click return and have the codes appear with the hyphens.

1 other issue is that some excel documents will have hundreds of these codes
the final number counts up from 0200 to 0201 etc in this case. but some of the other characters change from XX to ZZ for instance. so whilst im able to 'drag copy' to popoulate the codes. i will have to edit each one to replace XX with ZZ or ZZ with 01 to name two examples
any suggestions of how to speed up inputting these codes would be gratefuly recieved.

[Aardigspook]

Welcome to the forum.

There isn't any custom formatting for 'make this a letter' (unlike 'make this a number' which can use '0' or '?' or '#' depending on what's needed).
You can specify a particular letter, but not 'this must be letter, any letter'.

It's also worth noting that custom formatting only changes how Excel displays a number; it doesn't change how the number is actually stored.
For example, if you format AB123 as A-B-12-3 then copy the cell and paste it into Notepad, you'll get AB123, not A-B-12-3.

Taking both of those points, if you want to enter the code without hyphens and have Excel enter the hyphens, it would be best to do this in another column.
If you have your first code (without hyphens) in cell A2, put this in cell B2:

Please Login or Register  to view this content.

Then drag it down as far as you need.

It works like this:

LEFT(A2,3) = take the first 3 characters of A2
&"-"& = add a hyphen
MID(A2,4,3) = starting at character 4, take another three characters
repeat similarly for the other middle parts before finishing with
RIGHT(A2,4) = take the last 4 characters of A2

If the FTH-DSA-XX part stays exactly the same for all codes, then you could just enter the part after that in column A:

01FFA1200

and use this in column B instead:

Please Login or Register  to view this content.

Regarding your last issue, if there's a pattern to the changes from XX to ZZ etc, then it might be possible to semi-automate this (perhaps using IF statements or something) but we'd need to know the logic (i.e. the pattern) behind it.

Hope that helps.