Forum Home
Press F1
 
Thread ID: 51605 2004-11-25 08:23:00 XL Macros needed Muzz (1286) Press F1
Post ID Timestamp Content User
296194 2004-11-25 08:23:00 I have a couple of problems that have got me stumped! Firstly I have a long column of Part Numbers of 11 digits in length that have spaces between the numbers. I need a macro to run and get rid of the spaces The second macro I need is that I have a column of numbers of either 1 or 2 digits that I need to consist of 3 digits ie if it is number 3 it needs 2 leading 0's to end up 003 & the number 12 would need 1 zero added to end up 012.
I would really appreciate some help on this as I am still a 'macro apprentice' 		Muzz (1286)
296195 2004-11-25 08:37:00 > I have a couple of problems that have got me stumped!
> Firstly I have a long column of Part Numbers of 11
> digits in length that have spaces between the
> numbers. I need a macro to run and get rid of the
> spaces

Google is your friend:
Search[/url

Answer:
[url]http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20861255.html (www.google.co.nz)

> The second macro I need is that I have a
> column of numbers of either 1 or 2 digits that I need
> to consist of 3 digits ie if it is number 3 it needs
> 2 leading 0's to end up 003 & the number 12 would
> need 1 zero added to end up 012.

You should be able to do this just by formatting the cell.

Select all the cells, then go to Format -- > Cells -- > Number Tab -- > Custom Category.

If you want a total of 3 digits, enter three 0's in the "Type" box

i.e.: 000

This would make:

1 = 001
2 = 002
23 = 023
123 = 123

etc. 		ninja (1671)
296196 2004-11-25 08:43:00 Hi, you don't need to do these with a macro. You can do what you want using some of the built-in functions of Excel.

For example, to insert leading zeroes into a number contained in cell A1, use this formula : =TEXT(A1,"000") {although it does convert the number to text, another option is the custom format sugegsted by ninja}

To strip the spaces out of the numbers, use this formula :
=SUBSTITUTE(A1," ","")

In the last formula the second argument has one space between double speech quotes and the third argument is just two speech quotes.

HTH, Andrew :) 		andrew93 (249)
296197 2004-11-25 08:53:00 Thanks for that chaps...Andrew93..that looks like what I need as I forgot to add that the columns have to end up as text to activate another macro further down the track. I will put that to the test tomorrow. Muzz (1286)
296198 2004-11-25 09:39:00 Hi , I dont think you need a macro either. :-)

Probably the quickest way is to select the cells then use Edit|Replace and enter a single space in the find and leave the other box blank an click Replace All. Then just enter a custom format. 		parry (27)
296199 2004-11-25 23:17:00 Parrys idea works well for stripping the spaces but inserting the leading zeros is a bit harder. It is compounded by the need to have the column formatted as TEXT rather than a number. Any more clues?? Muzz (1286)
296200 2004-11-26 03:42:00 Hi Muzz, the Custom Format will allow you to have leading zeros for display (but will not affect the actual value of the cell). See Ninjas post above. :-)

If you still have problems post again. 		parry (27)
296201 2004-11-26 03:47:00 Doh, too quick to post. If you want actual zeros in the values (rather than merely formatting then use Andrews formula Text(A1,"000") in another column where A1 is the target cell. When you have done that you will need to format the target column as text as leading zeros are not retained by Excel for non-text. Now copy the cells with the formula then paste special as values into the target column.

hth 		parry (27)
296202 2004-11-27 04:23:00 If you really want a macro then here you go. Select cells and run the macro.

To add the code below to a Module do the following:-
1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)

Sub ReformatCells()
Dim c As Range
'This macro places high order zeros within the cell value if you require that
'instead of just formatting. Select the cells then run this macro.

For Each c In Selection
If Not IsError(c) Then
'Remove spaces
c.Value = WorksheetFunction.Substitute(c.Value, " ", "")
'Format cell as text to retain high order zeros
c.NumberFormat = "@"
'Add high order zeros based on 3 digit number
If Not Len(c) >= 3 Then c.Value = Format(c, "000")
End If
Next c

End Sub 		parry (27)
1