How to segregate the text from columns

B

BlackPanther

New Member
Joined
May 11, 2015
Messages
21
I have a excel file with the following column , it needs to be segregated into date and month , but at some cells the format is different For eg : 19-Jan , and in some cells its Feb-36.

I tried using text to xolumns funtions & delimiter , but couldnt get the result . Can you please help me out ?

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18-Feb[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19-Jan
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18-Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-71
[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-34[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18-Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-70[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-33[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-69[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-32
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]31-Jan[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-30
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]29-Jan[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]28-Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-42[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18-Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-68[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]22-Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-41[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]22-Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-67[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-40[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-39[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-66[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18-Feb[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]13-Jan[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-65[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-38[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-64[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Jan-37[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-36[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]Feb-63[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11-Feb[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]21-Jan[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Are you using the Text Import Wizard OR simply copy / paste OR opening the file from its' source via Excel ??
I'd suggest, if you are not using it, to try the Import wizard
On the ribbon
Data>Get External Data>>From Text>>>follow prompts

There is a walk through here.....
Excel: Importing text files into Excel sheets

NOTE: step 2 of the process, where it asks for column Data format (top left corner)
 
Upvote 0
gaz_chops said:
Try
=IF(YEAR(A1)=2015,DAY(A1),MONTH(A1))
&

=IF(YEAR(A1)=2015,MONTH(A1),RIGHT(YEAR(A1),2))

When you load the file are you selecting "Text" for column data format?
Code: 
[TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]19-Jan[/TD]
[TD="width: 65, align: right"]19[/TD]
[TD="class: xl65, width: 65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]18-Feb[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19-Jan[/TD]
[TD="align: right"]19[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]18-Feb[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-71[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]71[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-34[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]34[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]18-Feb[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Jan-70[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65"]70[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Jan-33[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65"]33[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-69[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]69[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-32[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]32[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]31-Jan[/TD]
[TD="align: right"]31[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-30[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]30[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10-Jan[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]29-Jan[/TD]
[TD="align: right"]29[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]28-Feb[/TD]
[TD="align: right"]28[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-42[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]42[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]18-Feb[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Jan-68[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65"]68[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]22-Feb[/TD]
[TD="align: right"]22[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Jan-41[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65"]41[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]22-Feb[/TD]
[TD="align: right"]22[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]Feb-67[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65"]67[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thanks for your info , that made me rethink and this time i used text in delimitter and got the following result : and the result is perfect , provided in the highlighted cells it is showing an error , when i save it to csv it will go back to month & year For eg in this situation it will show 2-34 ( Feb - 34)

Well as i have the partial result , i need one last help , can this column be segregate the same way .
Now that the output is partially correct . I want the same to be segregated 1 & 19 in separate columns , Can we use a formula other than month & year / date . because theses are not month , year or dates .

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-18
[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-71[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-34
[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-18
[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-70[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-33[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-69[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-32[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-31[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-30[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-10[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-29[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-28[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-42[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks to all -
gaz_chops ,

user-online.png



Michael M
user-offline.png



I am a newbie & i found the solution through your help

Here is what i did , As gaz_chops asked whether i use "When you load the file are you selecting "Text" for column data format?" which i didnt earlier . i used text while delimiting and got the following result :

i got the neat result :

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-19[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-18[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-71[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-34[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-18[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-70[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-33[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-69[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-32[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-31[/TD]
[/TR]
[TR]
[TD="class: xl63"]2-30[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-10[/TD]
[/TR]
[TR]
[TD="class: xl63"]1-29[/TD]
[/TR]
</tbody>[/TABLE]
user-online.png



Now time to segregate the above ones. I thought of delimiting it once again & tada this is what i got :

Correct result

[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]1[/TD]
[TD="width: 64, align: right"]19[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="align: right"]68[/TD]
[/TR]
</tbody>[/TABLE]


Many thanks for the help mates.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
216
Peter_SSs
Peter_SSs
sparky2205
  • Question Question
COUNTIF with DATES
Replies
3
Views
196
sparky2205
sparky2205
M
  • Question Question
Sumifs based on dates and criteria
2
Replies
12
Views
396
Alex Blakenburg
A
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
640
skyh3ck
skyh3ck
J
  • Solved
removing 0s from output and cell staying in date format
Replies
5
Views
363
Joe4
Joe4

Share this page

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
Back
Top