Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Standard User bookey
(experienced) Wed 11-May-11 16:44:49
Print Post

Excel Issue


[link to this post]
 
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?

Paul
Standard User john2007
(legend) Wed 11-May-11 16:50:32
Print Post

Re: Excel Issue


[re: bookey] [link to this post]
 
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.
Standard User bookey
(experienced) Wed 11-May-11 16:51:27
Print Post

Re: Excel Issue


[re: john2007] [link to this post]
 
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?

Paul


Register (or login) on our website and you will not see this ad.

Standard User john2007
(legend) Wed 11-May-11 17:00:17
Print Post

Re: Excel Issue


[re: bookey] [link to this post]
 
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.
Standard User camieabz
(legend) Wed 11-May-11 17:48:59
Print Post

Re: Excel Issue


[re: bookey] [link to this post]
 
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.

~~~~~~~~~~


© Camieabz 2002-2011

Live BQM

My Broadband Speed Test
Anonymous
(Unregistered)Wed 11-May-11 17:49:05
Print Post

Re: Excel Issue


[re: bookey] [link to this post]
 
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.
Anonymous
(Unregistered)Wed 11-May-11 17:50:56
Print Post

Re: Excel Issue


[re: Anonymous] [link to this post]
 
Like buses you wait for one and then 2 answers come along together!
Standard User camieabz
(legend) Wed 11-May-11 17:55:07
Print Post

Re: Excel Issue


[re: camieabz] [link to this post]
 
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. smile

~~~~~~~~~~


© Camieabz 2002-2011

Live BQM

My Broadband Speed Test
Standard User philippercival
(committed) Wed 11-May-11 20:30:35
Print Post

Re: Excel Issue


[re: camieabz] [link to this post]
 
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,
Anonymous
(Unregistered)Thu 12-May-11 02:20:23
Print Post

Re: Excel Issue


[re: philippercival] [link to this post]
 
Find and replace ? (in the Editing toolbar)
Martin
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to