How to select characters from text string?

rlekkala

New Member
Joined
Feb 3, 2009
Messages
49
Hi folks I have text representing date with time etc, I need just the month and year without the date and time as shown below. Is there a simple formula or macros that could achieve this?


What I have

February 22, 2011 1:59:21 PM GMT-05:00
June 25, 2010 3:22:05 PM GMT-05:00
June 25, 2010 7:57:12 AM GMT-05:00

What I want

Feb-11
Jun-10
Jun-10
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
This will give you what you asked for, but the result is still text, not an actual date.

Excel Workbook
AB
1February 22, 2011 1:59:21 PM GMT-05:00Feb-2011
2June 25, 2010 3:22:05 PM GMT-05:00Jun-2010
3June 25, 2010 7:57:12 AM GMT-05:00Jun-2010
Sheet1
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello,

If you have comma & space after the day, try this;

=LEFT(A1,FIND(",",A1)+5)+0

Format the cell as mmm-yy
 

rlekkala

New Member
Joined
Feb 3, 2009
Messages
49
Thank You guys :)
Can this be written as a macros that auto populates a new sheet with the updated values? Also The values are shown in alphabetical order, so I manually replaced Jan-2010 to 01)Jan-2010, and so on and then13)Jan-2011 for Jan-2011
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672

ADVERTISEMENT

Hi Haseeb,

Your formula does not work. LEFT() returns a literal text, and not a number that can be coerced using +0.

rlekkala,

If you want the result in date format, then use the formula in B2 (cell formatted as mmm-yy). C2 is another method to get the required info, but in TEXT format.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">February 22, 2011 1:59:21 PM GMT-05:00</td><td style="text-align: right;;">Feb-11</td><td style=";">Feb-11</td><td style="text-align: right;;"></td><td style=";">Apr</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">June 25, 2010 3:22:05 PM GMT-05:00</td><td style="text-align: right;;">Jun-10</td><td style=";">Jun-10</td><td style="text-align: right;;"></td><td style=";">Aug</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">June 25, 2010 7:57:12 AM GMT-05:00</td><td style="text-align: right;;">Jun-10</td><td style=";">Jun-10</td><td style="text-align: right;;"></td><td style=";">Dec</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Feb</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jan</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jul</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jun</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Mar</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">May</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Nov</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Oct</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sep</td><td style="text-align: right;;">9</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=DATE(<font color="Blue">MID(<font color="Red">A2,FIND(<font color="Green">",",A2</font>)+2,4</font>),LOOKUP(<font color="Red">LEFT(<font color="Green">A2,3</font>),$E$2:$E$13,$F$2:$F$13</font>),MID(<font color="Red">A2,FIND(<font color="Green">" ",A2</font>)+1,FIND(<font color="Green">",",A2</font>)-FIND(<font color="Green">" ",A2</font>)-1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A2,4,FIND(<font color="Green">",",A2</font>),"-"</font>),6</font>)</td></tr></tbody></table></td></tr></table><br />
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hi Haseeb,

Your formula does not work. LEFT() returns a literal text, and not a number that can be coerced using +0.


Hi, Sandeep

Based on the OP it works fine for me.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">February 22, 2011 1:59:21 PM GMT-05:00</td><td style="text-align: right;;">22-Feb-11</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">June 25, 2010 3:22:05 PM GMT-05:00</td><td style="text-align: right;;">25-Jun-10</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">June 25, 2010 7:57:12 AM GMT-05:00</td><td style="text-align: right;;">25-Jun-10</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">",",A2</font>)+5</font>)+0</td></tr></tbody></table></td></tr></table><br />
 

rlekkala

New Member
Joined
Feb 3, 2009
Messages
49

ADVERTISEMENT

Haseeb,
You are correct! Your formula works fine :)
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
Hmm

That's really strange. It does not work for me.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">February 22, 2011 1:59:21 PM GMT-05:00</td><td style="text-align: right;;">#VALUE!</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">",",A2</font>)+5</font>)+0</td></tr></tbody></table></td></tr></table><br />
 

rlekkala

New Member
Joined
Feb 3, 2009
Messages
49
Can a macros be written that automatically adds a column C with the calculated values for all the cells in column B that have Value? And may be even format Column C to yyyy-mm?
Manually I could drag the below formula and change the format to yyyy-mm and it works perfect.


Worksheet Formulas
Cell Formula
B2 =LEFT(A2,FIND(",",A2)+5)+0
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top