If - Then Statements

penny37

New Member
Joined
Mar 21, 2002
Messages
3
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top