Results 1 to 7 of 7

Thread: Split the Number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    249
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Split the Number

    Hi All,

    Thanks in advance

    I have the following sheet

    Excel 2010 32 bit
    A
    1
    04
    2
    45
    3
    65
    4
    02
    5
    90
    6
    06
    Sheet: Sheet1

    Want the following result

    Excel 2010 32 bit
    A
    B
    C
    1
    04
    0
    4
    2
    45
    4
    5
    3
    65
    6
    5
    4
    02
    0
    2
    5
    90
    9
    0
    6
    06
    0
    6
    Sheet: Sheet1

    tried the following but not find the result

    1) IF(VALUE(MID($A2,COLUMNS($B$1:B1),1))=0,"",MID($A2,COLUMNS($B$1:B1),1))
    2) =MID($A2,COLUMNS($B$1:B1),1)


    Help pls
    Last edited by Vishaal; Sep 19th, 2019 at 04:20 AM.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,041
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Split the Number

    maybe Text to columns?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Split the Number

    If the values are Text then try columns B:B but if the values are Numbers formatted to show leading zeros then try columns F:G and if you haven't given us a fully representative sample, then please do so.

    Split

    ABCDEFG
    10404 0404
    24545 4545

    Spreadsheet Formulas
    CellFormula
    B1=LEFT(A1)
    C1=RIGHT(A1)
    F1=LEFT(TEXT(E1,"00"))
    G1=RIGHT(E1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,317
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Split the Number

    Quote Originally Posted by Peter_SSs View Post
    If the values are Text then try columns B:B but if the values are Numbers formatted to show leading zeros then try columns F:G

    Split

    A B C D E F G
    1 04 0 4 04 0 4
    2 45 4 5 45 4 5

    Spreadsheet Formulas
    Cell Formula
    B1 =LEFT(A1)
    C1 =RIGHT(A1)
    F1 =LEFT(TEXT(E1,"00"))
    G1 =RIGHT(E1)
    Another set of formulas that will work whether the numbers are text or formatted numbers...

    Left Digit: =INT(E1/10)

    Right Digit: =MOD(E1,10)

    Note: These formulas return numeric values, not text values.
    Last edited by Rick Rothstein; Sep 19th, 2019 at 05:16 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Mar 2019
    Posts
    249
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split the Number

    Thanks Peter_SSs Ji


    Its working for me
    F1 =LEFT(TEXT(E1,"00"))


  6. #6
    Board Regular
    Join Date
    Mar 2019
    Posts
    249
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split the Number

    Thanks Rick Rothstein JI

    its working for me
    Left Digit: =INT(E1/10)


  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Split the Number

    Sounds like they are numbers then & Rick has the simplest solution.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •