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 ?

19-Jan
18-Feb
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
19-Jan
18-Feb
Feb-71
Feb-34
18-Feb
Jan-70
Jan-33
Feb-69
Feb-32
31-Jan
Feb-30
10-Jan
29-Jan
28-Feb
Feb-42
18-Feb
Jan-68
22-Feb
Jan-41
22-Feb
Feb-67
Feb-40
Jan-39
Jan-66
18-Feb
13-Jan
Feb-65
Feb-38
Jan-64
Jan-37
Feb-36
Feb-63
11-Feb
21-Jan

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Actually i need to segregate feb & 36 , This is not about month and year . I tried to load a text document in excel The actualy text file is 2 - 36 , but when loaded in excel it will show feb - 36 .
 
Upvote 0
BlackPanther said:
Actually i need to segregate feb & 36 , This is not about month and year . I tried to load a text document in excel The actualy text file is 2 - 36 , but when loaded in excel it will show feb - 36 .
Click to expand...

Your OP says "it needs to be segregated into date and month", hence my question.
How did you load the file into Excel?
 
Upvote 0
i mentioned , it needs to be segregated into month & date . depending on the cells i pasted. the actual file is a text file , i loaded it by opening excel and delimiting it ( by using text to columns)
 
Upvote 0
Did you format the column as Text then load the text file ??.
Can you give us the original data rather than what you tried to modify please ??
 
Upvote 0
If you have to work with the datat you have posted, maybe this .....either in seperate columns or in one column, whichever you prefer



Excel 2007
ABCD
119-Jan01- 150115
218-Feb02- 150215
319-Jan01- 150115
419-Jan01- 150115
519-Jan01- 150115
619-Jan01- 150115
719-Jan01- 150115
819-Jan01- 150115
919-Jan01- 150115
1019-Jan01- 150115
1119-Jan01- 150115
1219-Jan01- 150115
1319-Jan01- 150115
1418-Feb02- 150215
15Feb-7102- 710271
16Feb-3402- 340234
1718-Feb02- 150215
18Jan-3301- 330133
19Feb-6902- 690269
20Feb-3202- 320232
2131-Jan01- 150115
22Feb-3002- 300230
2310-Jan01- 150115
2429-Jan01- 150115
2528-Feb02- 150215
26Feb-4202- 420242
2718-Feb02- 150215
28Jan-6801- 680168
2922-Feb02- 150215
30Jan-4101- 410141
3122-Feb02- 150215
Sheet3
Cell Formulas
RangeFormula
B1=TEXT(A1,"mm")& "- "&TEXT(A1,"yy")
C1=TEXT(A1,"mm")
D1=TEXT(A1,"yy")
 
Upvote 0
Michael M said:
If you have to work with the datat you have posted, maybe this .....either in seperate columns or in one column, whichever you prefer


Excel 2007
ABCD
1
19-Jan01- 150115
2
18-Feb02- 150215
319-Jan01- 150115
419-Jan01- 150115
519-Jan01- 150115
619-Jan01- 150115
719-Jan01- 150115
819-Jan01- 150115
919-Jan01- 150115
1019-Jan01- 150115
1119-Jan01- 150115
1219-Jan01- 150115
1319-Jan01- 150115
1418-Feb02- 150215
15Feb-7102- 710271
16Feb-3402- 340234
1718-Feb02- 150215
18Jan-3301- 330133
19Feb-6902- 690269
20Feb-3202- 320232
2131-Jan01- 150115
22Feb-3002- 300230
2310-Jan01- 150115
2429-Jan01- 150115
2528-Feb02- 150215
26Feb-4202- 420242
2718-Feb02- 150215
28Jan-6801- 680168
2922-Feb02- 150215
30Jan-4101- 410141
3122-Feb02- 150215

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3​

Worksheet Formulas
CellFormula
B1=TEXT(A1,"mm")& "- "&TEXT(A1,"yy")
C1=TEXT(A1,"mm")
D1=TEXT(A1,"yy")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Thanks for the reply , but please check the cells highlighted in green , when we segregate it comes as 15 in cells B & D . but i want 19 ( which i am not able to segregate )
 
Upvote 0
i tried changing the code to :

=TEXT(A1,"mm")& "- "&TEXT(A1,"dd") instead of yy following is the result ( the highlighted ones give different answer ) for eg :
Feb-69
02- 010201

<tbody>
</tbody>
it wont retrive 69 , but it will show 01

I am not dealing with month & year . when we load the text file , it comes as month and year . in the actual test file the first line 1- 19 , but when we load the file to excel it shows 19 - Jan.

19-Jan01- 190119
18-Feb02- 180218
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
19-Jan01- 190119
18-Feb02- 180218
Feb-7102- 010201
Feb-3402- 010201
18-Feb02- 180218
Jan-7001- 010101
Jan-3301- 010101
Feb-69
02- 010201
Feb-3202- 010201
31-Jan01- 310131
Feb-30
02- 010201
10-Jan01- 100110
29-Jan01- 290129
28-Feb02- 280228
Feb-42
02- 010201
18-Feb02- 180218
Jan-6801- 010101

<tbody>
</tbody>
 
Last edited:
Upvote 0
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]

 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • 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
347
skyh3ck
skyh3ck
J
  • Solved
removing 0s from output and cell staying in date format
Replies
5
Views
210
Joe4
Joe4
ExcelNewbie2020
  • Solved
transpose data
2
Replies
15
Views
372
kvsrinivasamurthy
K
J
  • Solved
iferror function conditional formatting
Replies
1
Views
154
Joe4
Joe4
S
  • Solved
need to add * if score are less then 50
Replies
5
Views
481
JamesCanale
JamesCanale

Forum statistics

Threads
1,216,473
Messages
6,130,838
Members
449,597
Latest member
buikhanhsang

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