Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How can excel tell that 59 is 50 and 9 (I need to act on the

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Robert P. Wagner
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assume a1 value is 59. I need to determine that the number is 50 and 9. Then I will do something using the 50 and then something using the 9... It will always be a two digit number (01 through 59). Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What you could say is, if A1 = 59, then

    =LEFT(A1)*10 will give you 50
    =RIGHT(A1) will give you 9.

    Rgds
    AJ

    [ This Message was edited by: AJ on 2002-03-23 08:37 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Robert P. Wagner
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great Aj just did a search and found the left and right but appreciate your help also. I didnt thing about the a1 * 10 for the 50 part.. tks once again

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    First digit
    =LEFT(Range,1)*10

    Last digit
    =RIGHT(Range,1)+0

    I assume that you handle the leading zeros already. The LEN of the cell value would need to be checked if not.

    The +0 forces Excel to treat the value as a number, which it wouldn't if the argument cell was text.

    Regards,
    Jay

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-23 08:37, AJ wrote:
    What you could say is, if A1 = 59, then

    =LEFT(A1)*10 will give you 50
    =RIGHT(A1) will give you 9.

    Rgds
    AJ

    [ This Message was edited by: AJ on 2002-03-23 08:37 ]
    A bit simpler:

    =LEFT(A1,2)+0
    =RIGHT(A1)+0

    to turn the left and right bits into numbers.

    Aladin

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

    Default

    Hi Aladin:
    Following up on this thread, rpwagner wants to see the place 10-digit 5 as 50, so your first line would be:

    =(LEFT(A1)+0)*10, and the 0-place digit then
    =RIGHT(A1)+0


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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

    Default

    Actually, this works

    =LEFT(A1)*10
    =RIGHT(A1)+0

    The '+0' is just an example math operation, therefor, there's no need to '+0' and then '*10' to get a real number
    Regards,

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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You state that information is numbers.
    If they are not text, consider the following:

    59 in F2 =INT(F2/10) gives 5

    and =MOD(F2,10) gives 9


  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-23 08:57, Yogi Anand wrote:
    Hi Aladin:
    Following up on this thread, rpwagner wants to see the place 10-digit 5 as 50, so your first line would be:

    =(LEFT(A1)+0)*10, and the 0-place digit then
    =RIGHT(A1)+0
    I see I misread rpwagner's query:

    =LEFT(A1)*10 is just right.

    Aladin

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

    Default

    Thanks Juan:
    You are right -- there is no need to add that 0, even if the original entry was a text entry!

Some videos you may like

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
  •