Creating a 10-digit number and filling in the missing characters

[acrawford]

I'm working on spreadsheet that has an account number field that is supposed to be 10 characters. However, not all of the account numbers are the same length. Some longer and some shorter. I need the end of the account numbers that are missing digits to get filled in with 9's. Does anyone know of a simple way to do this without code? Thanks for any replies!

 

[GTO]

maybe?

Excel Workbook
	A	B
2	123	1239999999
3	123456	1234569999
4	1234567890	1234567890
5	12345678901	12345678901
Sheet3

 

[andiemac]

I came up with almost the same thing...
Assuming your accounts are in column A:

=A1&RIGHT("9999999999",10-LEN(A1))

 

[p45cal]

Excel Workbook
	A	B
1	1231231231	1231231231
2	1231231	1231231999
3	12	1299999999
4	123123	1231239999
Sheet17

Formula in B1 copied down.

 

[acrawford]

You're fabulous!! Thanks so much! Saved me a ton of time!

 

[shg]

One more:

=LEFT(A1 & 9999999999, 10)

 

[acrawford]

All of these formulas work wonderfully and I can't thank each person enough for the prompt responses!

I have another issue I've ran into...is there a way to get the numbers inserted at the beginning of the account number instead of at the end?

 

[shg]

=right(9999999999 & a1, 10)

 

[acrawford]

That's it!! I've tried and tried at this and I'm so aggrivated with myself for not getting this. Again, thanks for your help and time. Truly appreciate it!!!