Simple macro needed to format and update a column of values

[hk106]

Hi,

I've never written an Excel macro before, so I'm a little confused as to how I would begin with this.

The task seems pretty simple. I need to iterate through a column of values.

For each value:
* Format as text
* If the value contains an apostrophe as its first character, remove the apostrophe

Thank you for your help!

[Richard Buttrey]

Hi,

Are the values you mention all 'numbers' that appear as text and you want to see real numbers?

If so just enter the number 1 somewhere. Copy it. Select your column of values and then use Paste Special Multiply.

Regards

[hk106]

Richard,

Actually, the problem is that the Excel file removes leading zeros, so they are brought in with a preceding apostrophe. Then the format is changed to text before the apostrophe gets stripped away to prevent from losing the leading zeros.

I could easily resolve this with a formula, but a macro needs to be created because this will be a continuing process and the department that will use the file would benefit from being able to use a macro as opposed to using formulas.

[scantron]

Would something like this work?

Please Login or Register  to view this content.

Just add a 0 for every number you need. You DONT need to convert to text or include an apostraphe. Assuming you want all your numbers to be the same number of leading (before decimal) digits, this should work.

You can throw that in a vba macro or just add a custom format that is 5 zeros.

[hk106]

scantron,

Actually, I'm working with codes that can have all kinds of values ("2835K8", "000F83", "028374"). I have no way of knowing if zeros will be at the beginning of a code, or how many. I just can't have the leading zeros removed, because they're part of the code.

[hk106]

So can anyone help with this?

Thanks.

[hk106]

Anyone? Please?

[hk106]

***bump***

[hk106]

***bump***

[ChemistB]

Okay, so you want the final value to be text, correct?
I'm only a beginner with VBA but try this code

Please Login or Register  to view this content.

Does that work for you?

[hk106]

ChemistB,

Thanks for the response.

I think you're probably close. Two things:

1) I have no way of knowing how many records there will be, so is there a way to iterate through a column until there's no value?
2) After formatting the value as text, the macro must see if the value has a leading apostrophe. If it does, the apostrophe must be removed.

Thanks

[hk106]

***bump***

[hk106]

Does anyone know how to create a simple macro on this forum?

[Richard Buttrey]

Which apostrophe do you mean?.
All text has an apostrophe by default, (or a " or ^) depending how its formatted. You can't remove those.
If you mean the character ' then just use

=IF(LEFT(A1)<>"'",A1,RIGHT(A1,LEN(A1)-1))

Regards

[hk106]

Yes, but how would one structure this in a macro that loops through a column with an undetermined number of values?

[Richard Buttrey]

Personally I'd avoid a looping VBA.
Always use standard excel functionality in preference and only resort to VBA if necessary and even then only use a loop as a very last resort.

Just copy that formula down a spare column, then copy it and paste special values back on top of the original.
If you really must have a macro then perform the same functionality.
e.g. supposing column D contains the values with leading apostrophes

Please Login or Register  to view this content.