SOLVED

Number format with wildcard letters

Copper Contributor

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
best response confirmed by Manasseh_Jackson (Copper Contributor)
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)
1 best response

Accepted Solutions
best response confirmed by Manasseh_Jackson (Copper Contributor)
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)

View solution in original post