| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
On Dec 7, 9:09 pm, wrote:
On Dec 7, 3:41 pm, Gord Dibben <gorddibbATshawDOTca wrote: MEAD Nothing wrong with your posted method but just for info, it can be made much more simple using one helper column. Assuming 00xxx data in column A In an adjacent blank column enter ="Z" & A1 Double-click to fill down. Then do the copy/paste special bit. Gord Dibben MS Excel MVP On Fri, 7 Dec 2007 15:02:00 -0800, MEAD5432 wrote: Assuming the 0 is at the beginning of the information in the field so it appears like 0XXXX..., you could try this alternative, non-VBA route: I assume that the existing data you want to add to is now in column A. -- Insert two columns in front of the field you want to edit. This gives you two blank columns A, B and then your existing data would be in column C. -- Type the new leading character, say "Z", you would like at the beginning in the first cell in the column B. -- In the empty column, at the beginning of your data (probably the second row) type: =CONCATENATE($B$1,C2). This will add the prefix so your data looks like: "Z0XXXX..." -- Copy this all the way down the column to the end of the data. -- Highlight all of column A and copy. -- Highlight all of column B, right click and select "Paste Special..." -- Select "Values" and click "OK" -- Delete column A. You can also delete the column with the existing data (Column C in the steps above) if you desire. Since you pasted the values, there is no formula to mess with your new data.- Hide quoted text - - Show quoted text - I am also trying to do something similar but I can only get it to work in one cell How do I copy it to all cells in that cloumn? You said Double click to fill down but what am I supposed to be double clicking? Sorry I am a rookie at this.- Hide quoted text - - Show quoted text - Nevermind I figured it out thanks for the help |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I format a field in Excel to allow for leading zeros? | Excel Discussion (Misc queries) | |||
| Leading zeroes in ZIP field | Excel Discussion (Misc queries) | |||
| How do I force a leading zero character eg 07817 | Excel Discussion (Misc queries) | |||
| How can I prefill a text field with leading 0s? | Excel Worksheet Functions | |||
| How to keep leading zero without changing field to text field? | Excel Discussion (Misc queries) | |||
Threaded Mode
