Field length 14 digits long with the end 4 characters static

[CLoos]

I am trying to update a field so it is always 14 digits long. The issue I am having is the first 10 digits need to have a trailing space to equate to 10 digits without moving the last 4 characters so in total it is always 14 digits long. Example below. I played around with the left/len formula without success.

1111111111bb01
111bb01
111111bb01
11111bb01
1111111111bb01
11111111bb01
111111111bb01

 

[Hui]

What about using a custom Number format of
0000000000"bb01"

 

[bosco_yip]

Or, in B1 copied down :

=RIGHT("0000000000"&A1,14)

Regards
Bosco

 

[Deepak]

=right(rept(x,14)&a1,14)

Same as basco, just little sorter.

Replace x with your choice char.

 

[CLoos]

I should have been more clear. The numbers can have a mix of letters and I need it to have spaces between the standard stock number and the trailing 4 alpha/numeric identifier. Is it possible? The example above collapsed my numbers together perhaps this picture is a better representation.

 

[bosco_yip]

I should have been more clear. The numbers can have a mix of letters and I need it to have spaces between the standard stock number and the trailing 4 alpha/numeric identifier. Is it possible? The example above collapsed my numbers together perhaps this picture is a better representation.

View attachment 50421

In B1, copied down :

=REPLACE(A1,MIN(FIND({1,2,3,4}+{0;5},A1&1/17)),,REPT("0",14-LEN(A1)))

Regards
Bosco

 

[Hui]

I think it should be this mess


B2: =LEFT(A2,MIN(10,IFERROR(FIND(" ",A2),LEN(LEFT(A2,LEN(A2)-4)))))&REPT("-",10-MIN(10,LEN(LEFT(A2,IFERROR(FIND(" ",A2),LEN(LEFT(A2,LEN(A2)-4)))))))&RIGHT(A2,4)

Change the "-" for a space

 

[Deepak]

View attachment 50427

In B1, copied down :

=REPLACE(A1,MIN(FIND({1,2,3,4}+{0;5},A1&1/17)),,REPT("0",14-LEN(A1)))

Regards
Bosco

Too good buddy! 1/17

 

[bosco_yip]

As per post #.7 Hui's data layout, in B2 copied down :

=REPLACE(A2,LEN(A2)-3,,REPT("0",14-LEN(A2)))

Change the "0" for a space

Regards
Bosco

 

[shrivallabha]

View attachment 50433

As per post #.7 Hui's data layout, in B2 copied down :

=REPLACE(A2,LEN(A2)-3,,REPT("0",14-LEN(A2)))

Change the "0" for a space

Regards
Bosco

Only thing to add to this excellent formula is change the cell font to "Courier" or similar fixed pitch font. It will make representation uniform.