If - Then Statements
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: If - Then Statements

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com