Forum Discussion

Manasseh_Jackson's avatar
Manasseh_Jackson
Copper Contributor
Feb 07, 2019
Solved

Number format with wildcard letters

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-...
excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Feb 07, 2019
    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)
JKPieterse's avatar
JKPieterse
Silver Contributor
Feb 07, 2019
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)

Resources