remove last character from a column

[AS400Excel]

I want to delete the last character from "each cell in a column". Can some one provide the code for this kind of a macro?.

Thanks

 

[AS400Excel]

Macro is working.
When I created "Oak tree " formula and copied it across I got "circular reference" error and I fixed it.
But Copy,Paste special is not working.

Am I missing something, is it working for you guys?.

Thanks

 

[Zack Barresse]

Don't put it in the same cell you are referring to. Use an intermediary column. So if you have A1 in your formula, do not use column A to put the formula in, use column B:IV (choose one).

 

[KenWright]

assuming you had data in cells A1:A10, in cell B1 put the formula you were given. Copy that formula down to cells B1:B10. You will now see the same data in Col B as A with the excpetion of the last character.

Select B1:B10, do edit / Copy then Edit / Paste Special / Values.

You can either paste special over the original values in Col A and then delete Col B data, or do something else.

 

[AS400Excel]

This works when I copy from an column to an other column.
I cannot ask the user to do this.
The requirement is copy a worksheet as a whole and paste to an other template which has this formula.
When I do the Copy the whole worksheet and paste to this template, it's not working.

Help please.

Thanks for the inputs so far.

 

[Zack Barresse]

I might suggest attaching a macro routine to this template. Then after import, run the routine (Alt + F8).

 

[AS400Excel]

You know the users!!
I convinced them to do the copy and paste to a template and now if I ask them to do an other extra step of running macro, they do not like me.
This is an AS400 report and I have it in CSV and when I create CSV file from an AS400 report the leading zeros are truncating as csv treating this field as a number and I do not have control over that.
To avoid treating it as number I'm adding a char field to the end and when they copy and paste to a template I want the last char to do and the column in template is set a TEXT and thus I have the right data(with leading zeros) in the text format.

This is what I want to do.
Yes, your macros is definitely a good solution but.... user??

 

[AS400Excel]

sorry typo in my last posting.
I meant to say "I want the last char to "GO""

 

[KenWright]

What exactly are you trying to do?

So far I think you are generating a report from AS400. That report is a csv file. What do you then want to do with the data in that csv file, ie do you want to get the data into Excel but in text format and then do something with it, or are you simply dumping into Excel to edit it?

If the file is OK when it leaves AS400, then just change the extension from .csv to .txt, use the import wizard to open it in excel and specify all the import fields as text.

 

[AS400Excel]

When I dump the data from the csv file to .xls, I want the last char in that column be removed but I cannot copy and paste column by column, rather I want to copy the whole csv(CTRL+A,CTRL+C) and do paste special into the template.

I cannot just convert .csv to .txt because user wants report with some headings ,bold etc.,and so I am using template.

 

[KenWright]

If you are going to cpy the entire sheet and paste it into another, then you have no possibility of there being a formula in the destination sheet to handle tyhis becuase it would intsantly be wiped out.

That leaves you the choice of handling it by creating formulas once it is in the sheet, which I would expect the users to barf all over.

Given that i would suggest you have a single macro that does the lot for them, which means there is no need to do any copying and pasting at all, so the only thing the users would have to do is run the macro. Then have the macro do anything that need doing. If you want give them a button on their toolbar to do this with, but at the end of the day they need to do something, or you do it all your end and spit out the finished reports to them.