Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
I need an automated way to add a character before a leading zero in a field.
I am importing information into excel from one software, manipulating the information so I have only what I need and then exporting the information into another software. We created a macro to keep the leading zero in Excel but the software we are importing the information into does not allow leading zeros. =( So we have to add a character to before the leading zero. I am currently using Excel 2003. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
Try something like this:
Will concatenate a "X" to all selected cells. Excludes cells with formulas and empty cells. Change "X" to whatever character you want. Sub AddX() Dim cell As Range For Each cell In Selection.SpecialCells(xlCellTypeConstants) cell.Value = "X" & cell.Value Next cell End Sub Select the cells where you want this to happen and rum the macro. -- Biff Microsoft Excel MVP "Melin" wrote in message ... I need an automated way to add a character before a leading zero in a field. I am importing information into excel from one software, manipulating the information so I have only what I need and then exporting the information into another software. We created a macro to keep the leading zero in Excel but the software we are importing the information into does not allow leading zeros. =( So we have to add a character to before the leading zero. I am currently using Excel 2003. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
Try this small macro:
Sub xit() For Each r In ActiveSheet.UsedRange If IsEmpty(r) Then Else If Left(r.Text, 1) = "0" Then r.Value = "x" & r.Text End If End If Next End Sub -- Gary''s Student - gsnu200761 "Melin" wrote: I need an automated way to add a character before a leading zero in a field. I am importing information into excel from one software, manipulating the information so I have only what I need and then exporting the information into another software. We created a macro to keep the leading zero in Excel but the software we are importing the information into does not allow leading zeros. =( So we have to add a character to before the leading zero. I am currently using Excel 2003. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
Sub addchar()
Set myrange = Range("D1:D10") j = 1 For Each mycell In myrange Cells(j, 4) = "x0" & mycell j = j + 1 Next End Sub Change "D1:D10" to the Range you needed Change j = 1 to j = N where N is the row of the first cell of your range Change Cells(j, 4) to Cells(j, M) where M is the column number of your range ) (A=1, B=2. etc) Change "x0" to whatever character you want followed by 0 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Melin" wrote in message ... I need an automated way to add a character before a leading zero in a field. I am importing information into excel from one software, manipulating the information so I have only what I need and then exporting the information into another software. We created a macro to keep the leading zero in Excel but the software we are importing the information into does not allow leading zeros. =( So we have to add a character to before the leading zero. I am currently using Excel 2003. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
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. "Melin" wrote: I need an automated way to add a character before a leading zero in a field. I am importing information into excel from one software, manipulating the information so I have only what I need and then exporting the information into another software. We created a macro to keep the leading zero in Excel but the software we are importing the information into does not allow leading zeros. =( So we have to add a character to before the leading zero. I am currently using Excel 2003. Any help would be appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add character before leading zero in a field
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. |
#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 |
Reply |
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) |