Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cell names | Excel Discussion (Misc queries) | |||
Insert a number of rows based on a value in a cell on active row | Excel Discussion (Misc queries) | |||
Cell will not format numbers correctly for a 13 digit custom barc. | Excel Worksheet Functions | |||
spread the number 123.45 so each digit goes to a different cell | Excel Worksheet Functions | |||
Insert value of a cell as a filename | Excel Worksheet Functions |