• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Each digit to be separated and entered in a separate column

J

jeo varghese

Member
Dear all,

To print date on check, Dates are imported to excel from database as mm/dd/yyy.
Digits have to be separated and split each digit in a separate column.
Please find the attached file and help me.
Kindly look at this issue...
With regards
Jeo
 

Attachments

Hi Jeo,

One possible solution:

  • Select cells E5:L5.
  • Press F2 to enter in to Edit mode.
  • Put formula =MID(C5,{1,2,4,5,7,8,9,10},1)
  • Press Ctrl+Shift+Enter.

Regards,
 
@Debraj


Lolz....Didn't noticed that....:DD:DD:DD
That's Jeo's style of saving money, give a cheque of this date....it will never gets cashed.




Regards,
 
Last edited:
@jeo varghese
If dates are always in TEXT format. then..
=MID(SUBSTITUTE($C5,"/",""),COLUMN(A1),1)*1
other wise..
=MID(TEXT($C5,"ddmmyyyy"),COLUMN(A1),1)*1


Drag rightward..
 
Dear Sir,
Thanks a lot,
A small correction needed in this formula that is , if the dates less than 10 its not work.
If dates 10- 30 its works,
Pls stay with me ....
With regards
jeo
 
Hi Varghese ,

The formula is to be array entered , using CTRL SHIFT ENTER , in one cell E6 ; then copy the formula in E6 , by doing CTRL C , and paste it into the other columns' cells F6 , G6 ,....

Narayan
 
I am little perplexed with your data. If it is coming from database then for some reason it does not seem to be consistent (The date 31st Feb 2014 is absurd which is what Debraj has pointed out). Due to incorrect date format Excel has aligned it to Left. Are there some more dates with Left alignment but are not absurd.

You may not feel the heat with the dates such as 31/12/2014 as you can clearly distinguish between day and month but dates such as 05/04/2014 will give you massive pain as you will not be sure if it is 5th April or 4th May.

You can use formulas posted above as you wish but be careful with source data.
 
shrivallabha said:
The date 31st Feb 2014 is absurd which is what Debraj has pointed out)
Click to expand...
@shrivallabha
Hi!
I strongly doubt that 31st Feb 2013 isn't a valid date, isn't it the day where @r1c1 ships all the stuff for the Active Members team (t-shirts, cups, mugs, pendrives, pens, ...)?
For the Ninja one he uses another date, one day before I guess (so as to control better the AmEx Blacks and the golden iPads and...). :oops:
Regards!
 
You must log in or register to reply here.
Back
Top