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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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 .

1-19
1-19
1-19
1-19
1-19
1-19
1-19
2-18
2-71
2-34
2-18
1-70
1-33
2-69
2-32
1-31
2-30
1-10
1-29
2-28
2-42

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>
 
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 :

1-19
1-19
2-18
2-71
2-34
2-18
1-70
1-33
2-69
2-32
1-31
2-30
1-10
1-29

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>
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

119
119
119
218
271
234
218
170
133
269
232
131
230
110
129
228
242
218
168

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


Many thanks for the help mates.
 
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
391
skyh3ck
skyh3ck
J
  • Solved
removing 0s from output and cell staying in date format
Replies
5
Views
238
Joe4
Joe4
ExcelNewbie2020
  • Solved
transpose data
2
Replies
15
Views
428
kvsrinivasamurthy
K
J
  • Solved
iferror function conditional formatting
Replies
1
Views
173
Joe4
Joe4
S
  • Solved
need to add * if score are less then 50
Replies
5
Views
487
JamesCanale
JamesCanale

Forum statistics

Threads
1,217,394
Messages
6,136,353
Members
450,006
Latest member
DaveLlew

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