Excel Issue

I need to add a 0 to many codes in a excel table.

I currently have this per row: ABC-00-101
I need to change this to ABC-00-0101

Any ideas?

If no one has an excel solution.

If there are lots of consecutive rows I'd export them as text, use an editor macro to add the 0, and then re-import.

In reply to a post by john2007:

If there are lots of consecutive rows I'd export them as text, use an editor macro to add the 0, and then re-import.

Can you explain that a little more on the macro side please?

If the fields are regular in arrangement. e.g. they are all

xxx-xx-xxx

it is a repetitive editor task

e.g. move 7 characters right, insert 0, goto start of next line

A lot of 'programming' type editors let you save such a sequence as a macro (just means a simple program) and repeat it many times automatically.

Excel probably has a macro facility to do the same which is why I'd only suggest it if no one comes up with the excel solution.

Add two new columns to the right of the column with all the codes (or two rows under if in row format, and see rows for columns in the rest of the solution).

Assuming the first code is in cell A1, in cell B1 put:

=LEFT(A1,7) & "0" & RIGHT(A1,3)

Then drag the corner of A1 down to auto fill for the other codes.

Lastly, select all the new codes, cope and 'paste special' into the C column and paste values only. Then delete columns A and B.

The 'LEFT' function looks for the number of characters specified in a string from the left of the string, in a specified cell. 'RIGHT' does the same, but from the right. The code [ & "0" & ] allows text to be inserted in formulae.

Another example:

You want ABC-00-101 to say ABCDEF-00-A101

=LEFT(A1,3) & "DEF" & MID(A1,4,4) & "A" & RIGHT(A1,3)

The 'MID' is for extracting string data from the middle (-00- in this example). A1 is the source cell, 4 = the 4th character from the left and 4 for the number of characters.

Add a column with a formula

=left(a1,7)&"0"&right(a1,3)

copy down to bottom of spreadsheet

New codes appear

Select all and do a Paste Special of values to another column. This gets rid of the formulae.

Then copy the column back to the original column.

Like buses you wait for one and then 2 answers come along together!

P.S. - If you have other excel formulae which are dependents of the codes, then you'll have to change them to be dependents of the new code locations. Best to do this before deleting the old ones.

If you create a new column for the "calculated codes" to the right of your existing spreadsheet and fill it with formulae as outlined above,

1. The you can copy all the cells in the new column
2. Click at the top of the old code column (on the first code).
3. Right click and select PASTE SPECIAL from the context menu.

A box will pop up,

4. Tick the values box.
5. Click paste or OK.

This will copy all the new codes into the old location. Now just remove the calculated column form the right side of your spreadsheet,

Find and replace ? (in the Editing toolbar)
Martin