Forgotten Formula

Topic

New Reply

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)

Joel

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The Text-to-Columns works in earlier versions, also!
HTH!
Candy G

Reply | Quote