Results 1 to 5 of 5
Thread: Forgotten Formula

20121017, 19:31 #1
 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 123) 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; 20121026 at 15:48. Reason: Problem Solved Thanks

20121018, 00:43 #2
 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, TexttoColumns. All you need to do is specify what delimits the text.
Regards,
MariaMaria
Simmo7
Victoria, Australia

The Following User Says Thank You to simmo7 For This Useful Post:
joela44 (20121026)

20121019, 07:32 #3
 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.

20121024, 20:18 #4
 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

20121025, 17:02 #5
 Join Date
 Oct 2012
 Posts
 3
 Thanks
 0
 Thanked 1 Time in 1 Post
The TexttoColumns works in earlier versions, also!
HTH!
Candy G

The Following User Says Thank You to candybg For This Useful Post:
joela44 (20121026)