SOLVED
Home

Number format with wildcard letters

%3CLINGO-SUB%20id%3D%22lingo-sub-332296%22%20slang%3D%22en-US%22%3ENumber%20format%20with%20wildcard%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332296%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20recording%20form%20numbers%20in%20a%20column%2C%20which%20are%20very%20similar%20and%20can%20be%20sequential%20but%20include%2C%20one%20to%20three%20random%20system%20generated%20letters%3A%3C%2FP%3E%3CP%3Efor%20example%3A%3C%2FP%3E%3CP%3E3366-011-334J-P%3C%2FP%3E%3CP%3E3366-011-337L-C%3C%2FP%3E%3CP%3E3366-011-335M-P%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20characters%20that%20change%20are%20the%2010th-15th%20character%20of%20the%20number.%20Is%20there%20a%20way%20to%20input%20a%20wildcard%20letter%20character%20in%20number%20formatting%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-332296%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332351%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20format%20with%20wildcard%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332351%22%20slang%3D%22en-US%22%3EData%20netered%20like%20this%20in%20a%20cell%20always%20becomes%20text%20and%20there%20are%20no%20number%20formats%20available%20to%20format%20text%20in%20a%20cell.%20You%20could%20use%20a%20formula%20to%20format%20a%20cell%20entry%20like%203366011334JP%20so%20it%20shows%20as%203366-011-334J-P%3CBR%20%2F%3E%3DLEFT(A2%2C4)%26amp%3B%22-%22%26amp%3BMID(A2%2C5%2C3)%26amp%3B%22-%22%26amp%3BMID(A2%2C8%2C3)%26amp%3B%22-%22%26amp%3BMID(A2%2C11%2C1)%26amp%3B%22-%22%26amp%3BRIGHT(A2%2C1)%3C%2FLINGO-BODY%3E
Manasseh_Jackson
Occasional Visitor

I am recording form numbers in a column, which are very similar and can be sequential but include, one to three random system generated letters:

for example:

3366-011-334J-P

3366-011-337L-C

3366-011-335M-P

 

The only characters that change are the 10th-15th character of the number. Is there a way to input a wildcard letter character in number formatting? 

1 Reply
Solution
Data netered like this in a cell always becomes text and there are no number formats available to format text in a cell. You could use a formula to format a cell entry like 3366011334JP so it shows as 3366-011-334J-P
=LEFT(A2,4)&"-"&MID(A2,5,3)&"-"&MID(A2,8,3)&"-"&MID(A2,11,1)&"-"&RIGHT(A2,1)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies