How do I insert the same extra digit to each cell in a column?
 

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

1. Insert a temporary column next to the one you want to change. (If the
information is in A right click on column B and select insert)

2. Type this formula in B1...
=A1&"-3"

3. Copy the cell by dragging the bottom right corner all the way down to
the end of your list.

4. Highlight column B and select copy. Right click on A select paste
special. Select values.

5. Delete column B. (right click on B and select delete).

I am assuming you have it formatted as text. If not, let me see the custom
format you are using and I might be able to suggest another option.

"GR" wrote:

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
Cells(i,"A").Value = Cells(i,"A").Value & "-3"
Next i

HTH

Bob

"GR" wrote in message
...
Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

This seems to be working for me:
=LEFT(A10,FIND("-",A10,1))&0&MID(A10,FIND("-",A10,1)+1,255)&"-03"
--
tj


"GR" wrote:

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

I hit Post too quickly. You can put this in a blank column next to your data
(adjusting the cell references as needed) and copy down. Then, copy the cells
with the Formula and do Edit | Pate Special | Values.
If the data matches what you want, delete the original column.

If VBA is an option for you, Bob Phillips's post should do the trick.
--
tj


"tjtjjtjt" wrote:

This seems to be working for me:
=LEFT(A10,FIND("-",A10,1))&0&MID(A10,FIND("-",A10,1)+1,255)&"-03"
--
tj


"GR" wrote:

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

I am sorry. I didn't see the "0" added to 80. Use this formula...

=REPLACE(A1,6,0,"0")&"-3"

Hope this helps.

"Sloth" wrote:

1. Insert a temporary column next to the one you want to change. (If the
information is in A right click on column B and select insert)

2. Type this formula in B1...
=A1&"-3"

3. Copy the cell by dragging the bottom right corner all the way down to
the end of your list.

4. Highlight column B and select copy. Right click on A select paste
special. Select values.

5. Delete column B. (right click on B and select delete).

I am assuming you have it formatted as text. If not, let me see the custom
format you are using and I might be able to suggest another option.

"GR" wrote:

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

This completely answered the challenge. Plug in and went thru hundreds of
records less than a minute with complete accuracy!

"tjtjjtjt" wrote:

This seems to be working for me:
=LEFT(A10,FIND("-",A10,1))&0&MID(A10,FIND("-",A10,1)+1,255)&"-03"
--
tj


"GR" wrote:

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?

I'm glad it helped. Thanks for the feedback.
--
tj


"GR" wrote:

This completely answered the challenge. Plug in and went thru hundreds of
records less than a minute with complete accuracy!

"tjtjjtjt" wrote:

This seems to be working for me:
=LEFT(A10,FIND("-",A10,1))&0&MID(A10,FIND("-",A10,1)+1,255)&"-03"
--
tj


"GR" wrote:

Have column of numbers that must be converted to a new number by simply
adding same to number is two specific places. ex: 6200-80 must become
6200-080-3

How to do this w/out re-keying entire workbook?