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

SPLIT NUMBER OF DIGITS (DATE & NUMERIC) SEPARATELY

V

VDS

Member
@ Dear All,

I have the following data

Sr No ==Particulars== From ==To
1= Period under the Audit =01/04/12 ==31/03/13
2= Accounting year followed by Auditee =01/04/12 ==31/03/13
3 =Project undertaken =01/04/12 ==30/10/12
4 =Lapse date =01/11/12 ==31/03/13
5 =Defect Liabiity Period =01/06/12 ==30/03/13
6. = TIN No. = 00045680000
7. = PAN NO = AABCS7156H

For online filing/for computation of Tax purpose, the data has to be entered as per the attached format.

Like:
Period under the Audit : 01|04|12|31|04|13
PAN NO. A|A|B|C|S|7|1|5|6|H|
TIN NO : 0|0|0|4|5|6|8|0|0|0|0

Each digit to be separated and entered in a single column. The total number of records should be around 100+.

Could u please suggest a solution.


VDS
 

Attachments

  • upload_2013-11-28_14-12-20.png
    upload_2013-11-28_14-12-20.png
    50.8 KB · Views: 13
@VDS

you are using the word format i think it is better to use the Excel file, i will give you some details how to maintain
what is your format please fill all in the Sheet1 and link to Sheet2

with this formula

=MID($a1,COLUMN()-1,1)

and drag to right and down

for your better knowledge i attach a sample file please check the Sheet1 and Sheet2

Thanks
 

Attachments

  • Format_for_VDS.xlsx
    8.6 KB · Views: 15
@Patnaik,

Sir, Thanks, the function provided is Okay and solve the purpose. The output required in excel format only. Once output is generated, copy to any other application with table is easy due to OLE/clipboard. But the function is accepting text field only. How to break the dates "dd/mm/yy" format and fill in the second worksheet ?

Pleas advise.

VDS
 
@VDS

sorry i can't understand what you say if possible please upload a sample file then i will try to solve your problem

as per my understanding in the Govt. section there are some VBA Experts who build the format the date field should be enter as text format due to the Regional setting is different in every system to their system. as well as when we enter any date in excel then it will take as date format so in manually we have to convert them as text format say your date in Range A1 = 29-11-2013 (dd-mm-yyyy) in B1 just write as =TEXT(A1,"DD/MM/YYYY") then it will reflect as text format in B1 Column

Thanking You
 
@ Patnaik,

I am attaching herewith the pdf screenshot in which data to be filled. The format has been converted to excel. Precisely, it is the requirement of Delhi VAT and is required to fill up from time to time. The date fields is not on higher number, so can be typed manually. Most important is breaking of text data and filling in several parts of this format and that is now solved. Since my area of work is in projects, dates are important. This may even required in various vendor registration formats.

Hence, if data of 03/12/2013 is converted into text, it can again be split up with no "/" sign and all the 6 digits (in fact 8) should be in separate columns. Can we do this ?

Awaiting your suggestions for this.

Thank for support once again.


VDS
 

Attachments

  • upload_2013-11-29_13-33-39.png
    upload_2013-11-29_13-33-39.png
    149.1 KB · Views: 11
@VDS

yest it can be done using with Text Function say your Date in A1 as = 03/12/2013

simple type in B1 as = TEXT(A1,"ddmmyyyy")

then the date filed will be display as 03122013

Hope it clear now other wise please upload a sample file

Thanks
 
@Hellow Patnaik, Good morning,


In furtherance to my yesterday's message, I have a doubt, I have changed the text format of date into 3 different columns as per attachment with the MID FUNCTION.

For the time being it worked. How can I standardize the function /parameters correctly ?



VDS
 

Attachments

  • upload_2013-11-30_10-17-36.png
    upload_2013-11-30_10-17-36.png
    50.8 KB · Views: 11
@VDS

Sorry for my late replay, i can't under stand why the formula is not stand in your system any how
Please try another one

Say your date in Range A1 = 30/11/2013

in B1 = Text(a1,"dd")

in C1 = Text(a1,"mm")

in D1 = Text(a1,"yyyy")

Hope it's clear your problem

Thanks
 
@Patnaik,

The formula is very much clear. Only a minor clarification was pending. That also now sorted.

Keep on providing support.


VDS
 
Hi VDS..

Just one more approach..
B1 = "=Day(A1)"
C1 = "=Month(A1)"
D1 = "=Year(A1)"

You can custom format B1 & C1 as "00" if required..
 
You must log in or register to reply here.
Back
Top