Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Posts
    92
    Thanks
    2
    Thanked 6 Times in 6 Posts

    Forgotten Formula

    It's been years since I used Excel every day, (and before that Lotus 1-2-3) anyway, I need to be reminded how to put the concatenate formula. I pasted four columns from a program into excel and excel pasted everything into one. I need to break it into the columns is should have come in, Column 1 single digit, 2 four digits, 3, blank 4 First name 5 last name. So as I recall it's the number of characters from the right, but I'm lost. My brain got old and just can't remember the string! Thanks all!
    Joel

    Edit
    After more thought, maybe that's not the right formula either, vlookup? I don't know, what I do know is I'm getting older and my skills are fading from lack of use! Thanks again!

    Thanks to you all, I ended up being home sick the last few days, but I did read and text to columns is just right. When I was using it every day it was Lotus then 97, then XP, and finally office 2003 which I knew the best. By the time I got the "ribbon" version, I was an intermittent user, and I say thank you that the keystrokes all still work because I still don't have the full hang of these ribbons. Thank you again everyone. As always, this is the place to go for an answer! (Surprised I didn't hear from RG on this thread)
    Last edited by joela44; 2012-10-26 at 16:48. Reason: Problem Solved Thanks

  2. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Joel,

    If you have Excel 2007/2010 you can use Data, Text-to-Columns. All you need to do is specify what delimits the text.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  3. The Following User Says Thank You to simmo7 For This Useful Post:

    joela44 (2012-10-26)

  4. #3
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    Concatenate is the wrong function, it's used to join data from several columns. You'd want to use the Left, Mid and Right functions.
    Fortunately for us, we don't have to memorize how to use these functions. On the Home page, click the dropdown triangle beside the AutoSum feature, and choose "More Functions". Select the "Text" Category and Choose from Left, Mid and Right to have it prompt you on what to do next.
    Once everything is converted corectly, Just copy the data and paste back the values.

  5. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You may need a search function as well, for eg. the FIND function which is case sensitive or the SEARCH function which is case insensitive. For example if cell A3 contains

    ABCD.EFGH $123.45

    then it could be split up into 3 cells using:
    =LEFT(A3,FIND(".",A3)-1) resulting in ABCD Note length of this value given by the LEN() function is 4
    =MID(A3,FIND(".",A3)+1,FIND(" ",A3)-FIND(".",A3)-1) resulting in EFGH Note length of this value given by the LEN() function is 4
    =RIGHT(A3,LEN(A3)-FIND("$",A3)) resulting in 123.45 Note length of this value given by the LEN() function is 6

    regards
    Keith

  6. #5
    New Lounger
    Join Date
    Oct 2012
    Posts
    3
    Thanks
    0
    Thanked 1 Time in 1 Post
    The Text-to-Columns works in earlier versions, also!
    HTH!
    Candy G

  7. The Following User Says Thank You to candybg For This Useful Post:

    joela44 (2012-10-26)

Posting Permissions

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