Reverse of concatenate command
Posted by Fred Wildauer on May 22, 2001 1:11 PM
I have a cell that contains "May 2001" and I need to separate the information into two cells with "May" in one cell, and "2001" in the other cell. It has been several years since I have done this and I can not remember how it is done.
Please help!
Posted by Kevin James on May 22, 2001 1:17 PM
Use left and right string functions along with FIND to look for the blank.
Posted by IML on May 22, 2001 1:20 PM
Assuming your text is in date format, (in cell a1) you could use
month =TEXT(A1,"mmmm")
year =TEXT(A1,"yyyy")
If it is a text string you could use
month =LEFT(A1,FIND(" ",A1)-1)
year =RIGHT(A1,FIND(" ",A1))
Good luck
Posted by Aladin Akyurek on May 22, 2001 1:20 PM
Fred,
If your data consists of <month year> pairs, year is a 4-digit number, and the cell of the pair text formatted, you can use the following:
In B1 enter: =LEFT(A1,LEN(A1)-4)
In C1 enter: =RIGHT(A1,4)
Aladin
Posted by Dax on May 22, 2001 1:23 PM
Hi,
To extract just the year use the formula =Year(A1) of the date is in cell A1. As for extracting the month, it's a little more involved. Something like this will work: -
=CHOOSE(MONTH(A1),"J","F","M","A","M","J","J","A","S","O","N","D")
You'll have to change the month names, it's just that I didn't want to type all the date names in, just give you an idea.
Hope this helps,
Dax.
Posted by IML on May 22, 2001 1:26 PM
I didn't see someone else had answered already, any way, if it is text and the format is the same for all you could simplify with
=LEFT(A1,LEN(A1)-5) for month
=RIGHT(A1,4) for year
Posted by Dax on May 22, 2001 1:26 PM
Re: Ignore my method, it's overkill compared to IML's.....
Posted by IML on May 22, 2001 1:28 PM
Okay Aladin beat me to that one too! One step behind as usual!!
Posted by Aladin Akyurek on May 22, 2001 1:32 PM
Re: Okay Aladin beat me to that one too! One step behind as usual!!
How do I format the font size/ color of a drop down box in Excel 95. I need to make the font size larger so that it can be seen easier. Is this possible? If so, how? thanks for your help!!!
Posted by Dave Hawley on May 23, 2001 1:53 AM
Fred, can I have a shot ?
Hi Fred
If your cells are like "May 2001" you can select the column and then go to Data>Text to Columns, choose "Delimited", click "Next". Select "Space", click "Next" then "Finish"
DaveOzGrid Business Applications
Posted by Fred Wildauer on May 23, 2001 6:48 AM
First of all I would like to thank all of you for trying to help me with this problem! My choice of using a date as an example may have confused the issue.
Column A consists of "Lastname, Firstname". What I would like to do is separate this so that "Firstname" is placed into column B and "Lastname" is placed into column C.
Posted by IML on May 23, 2001 7:35 AM
First of all I would like to thank all of you for trying to help me with this problem! My choice of using a date as an example may have confused the issue.
For last name, you could use
=LEFT(A1,FIND(",",A1)-1)
and first name
=RIGHT(A1,FIND(",",A1))
If you don't want to use formula you could highlight your row A1 and goto
Data - Text to column
Hit next
Change the deliminater to Comma
Hit Next
Hit Finish
Now highlight your Firstname row and use to Find with a single space and replace with nothing (this will get rid of the space befor first name.
good luck...
