Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Sep Data

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

    Default

    ANY HELP OR IDEAS?

    I have a sheet with 70 char's in Colum A

    I need to put the first 30 char's in A, next 10 in B, and all remaining in C

    Thanks
    Matt

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    are you looking for a macro to do it... or something simple like copy/paste?
    Colin

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

    Default

    I would love to do a formula

    concatenate, but the reverse


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

    Default

    On 2002-05-08 18:45, mtann wrote:
    ANY HELP OR IDEAS?

    I have a sheet with 70 char's in Colum A

    I need to put the first 30 char's in A, next 10 in B, and all remaining in C

    Thanks
    Matt
    You can do it with DATA|Text_to_columns.

    Hoever if you must do it with a formula --
    if your entry is in cell A1, then

    in A2 ... =left(a1,30)
    in B2 ... =mid(a1,31,10)
    in C2 ... =right(a1,30)

    Regards!

    NB: After Chris' sharp eyes caught it, I corrected B2 to have 10 as the last argument (instead of 40).
    _________________
    Yogi Anand

    Edit: Deleted inactive website from hardcoded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 13:17 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 20:03, Yogi Anand wrote:
    On 2002-05-08 18:45, mtann wrote:
    ANY HELP OR IDEAS?

    I have a sheet with 70 char's in Colum A

    I need to put the first 30 char's in A, next 10 in B, and all remaining in C

    Thanks
    Matt
    You can do it with DATA|Text_to_columns.

    Hoever if you must do it with a formula --
    if your entry is in cell A1, then

    in A2 ... =left(a1,30)
    in B2 ... =mid(a1,31,40)
    in C2 ... =right(a1,30)

    Regards!

    Yogi, just 10 needed in that 2nd part

    so : in B2 ... =mid(a1,31,10)




  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

    Yogi, just 10 needed in that 2nd part

    so : in B2 ... =mid(a1,31,10)




    Hi Chris:
    10 in the last spot would in this case definitely work, but in most cases we would rather put a larger number, sometimes as much as 255, as cleanup all the way to the right end of the string.

    Edit: Chris, my apologies, I goofed, cell B1 needs only 10 characters -- 10 characters have nothing to do with the last split in cell of column C

    Regards!

    [ This Message was edited by: Yogi Anand on 2002-05-12 11:42 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    you mean in most cases where there are only 2 columns and the rest are in column 2 ?

    yours will never work in most cases of 3 columns !

    I see what you're thinking, just didn't want to confuse the person asking the question which relates to 3 columns spcifically

    Chris

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

    Default

    On 2002-05-11 03:27, Chris Davison wrote:
    Yogi,

    you mean in most cases where there are only 2 columns and the rest are in column 2 ?

    yours will never work in most cases of 3 columns !

    I see what you're thinking, just didn't want to confuse the person asking the question which relates to 3 columns spcifically

    Chris
    Hi Chris:
    My apologies -- Yes 30,10, and I meant the last 30 could be 30 , bigger or last bigger when I said 255, I was referring to the third column for picking up the right most values. Thanks for keeping me honest.

    Regards!

    [ This Message was edited by: Yogi Anand on 2002-05-12 11:33 ]

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
  •