If - Then Statements

[penny37]

What formula do I use to add a "0" to a cell that has less than six characters?

Example- if rewq is in cell a2 - then add a "0" to the end so it will be rewq0

[Tom Schreiner]

Hi Penny
What is the largest possible range we are talking about here?

I need to know which cells you are dealing with?

Also, are you familiar with macros?

[penny37]

Unfortunately, I am not familiar w/ macros.

Thanks for the reply- hope this helps.
The largest range would be 6 characters. The file that I am working w/ has 1000+ individuals. I tried to do a vlookup to enter shifts for individuals - however-I did not have an identical match in both worksheets. My spreadsheet has last name,first name middle initial plus user id's.

The spreadsheet that has the data I need (shifts) has last name first name (no middle initial) and badge #.

I thought it would be easier to add a 0 to the end of badge # so it would match the user id's in my spreadsheet. Standard user id is 6 characters long. If badge # is five characters - the user id is 5 characters plus a 0 at the end. Example - Badge rzle1 - user id rzle10. However, some badge id's are 6 characters long and will not need the additional 0 added. Example rf1238.

Any information would be much appreciated.

[Al Chara]

How about the following formula if you don't want to use macros:

=IF(MID(A2,6,1)="",CONCATENATE(A2,0),A2)
or
=IF(MID(A2,6,1)="",A2&"0")

This formula assumes that there are not spaces in the cell text (for example, rewq q...would not work). Just put the formula in the adjacent cell. This could also be done pretty easily in a macro.

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-03-22 18:55 ]

[ This Message was edited by: Al Chara on 2002-03-22 21:07 ]

[penny37]

Al,
Thank you! Your help has been much appreciated.

[Tom Schreiner]

Hi
This might not be the prettiest method, but will be very easy.

Create a copy of the sheet you need to append 0's to.

On the copy do the following:

In the topleft cell containing a badge number enter this formula

Where 'X' = OriginalSheet/Mirror Cell

formula here:

=if(len(trim(X)) < 6, x & "0",x)

Copy this cell and paste throughout the range of data you are in need of appending the 0's on the new mirror sheet.

Copy the whole data area on the mirror sheet, then pastespecial(Values) to the original sheet to update with new strings

If this still does not get it for you then post the following information:

The sheet name containing the badgenumbers
The cell reference, TopLeft badge number
The cell reference, BottomRight badge number

With this information, I or someone else can send you more explicit directions.

Good Luck!

[Yogi Anand]

To simply state the formula TsTom has given, if your entry is in cell A2, and your modified entry is in cell B2, then the formula in B2 will be

=IF(LEN(A2)<6,A2&"0",A2)

HTH

[Juan Pablo González]

If your data is numeric, you can custom format it like

000000

so it will have 6 digits, even if a number entered has less than this.