Removing Extra Details From Cells

[Guest]

I am preparing an e-return form in which i exported data from Tally 7.2(An
Accounting Software) to CSV format and Adjusted it in Excel but in One of the
column I have to Mantion "VAT TIN No" So while using tally VAT TIN No is
inserted like this "24xxxxxxxxx dt dd-mm-yyyy" but in IT return Form I only
have to show VAT TIN No not date (ie only 24xxxxxxxxx but not "dt
dd-mm-yyyyy") but while in exporting to CSV Format Tally Exports it in former
form (ie "24xxxxxxxxx dt dd-mm-yyyy") Now my excel sheet looks like following

Date Name VAT TIN No Amt
Tax Total
1-4-06 xyz 2456238942 dt 24-5-05 125634 1253
648666
1-6-06 abc 2456891236 dt 12-6-05 123456 1234
345678

but instead i want to show only tin no like following

Date Name VAT TIN No Amt
Tax Total
1-4-06 xyz 2456238942 125634
1253 648666
1-6-06 abc 2456891236 123456
1234 345678

so as u can see i have to do the same task to do in each cell so i want a
solution in which i can remove part of text after some places automatically i
have to change some 5000 cells like this so if some one can give me a Formula
or a Macro script

Thank You

 

[Guest]

I assume that VAT TIN No is in its own column, column C, starting in C2.

In an in-used column in the second row enter:
=LEFT(C1,FIND(" ",C1,1)-1) and copy down
The formula looks for the first blank in C2 and keeps only the number to the
left of this blank.

Finally, copy the "helper" column and paste/special/value back into column C.


You could also use Text to Columns to accomplish the same thing.

 

[Dave Peterson]

Another option:

Select that range to be fixed
edit|replace
what: _dt* (spacebar, dt, asterisk)
with: (leave blank)
replace all

Your results will be changed to numbers--leading 0's would be lost and strings
of numbers over 15 characters would break.

You could apply a custom numberformat (format|cells|number tab) to get the
leading 0's back.

But if you have strings of numbers greater than 15 characters, don't use this.