excel macro 1

[bcblair]

I have an excel file I import. It is a database with 600+ records. After I import one field is called itemno, It has&nbsp;&nbsp;7 to 8 digits with no spaces. I need to change it so it has a space between the fourth and fifth digit. I tried a macro by starting to record making it relative, then hitting F2(to edit), then the home key to go to the start of the field, then I hit the right arrow 4 times, then I add a space, then hit enter. It jumps to the next row. I thought this would work, but it repeats the number from the first cell over and over in every cell instead of adding a space to the data that was in that cell. I have tried everything I know.<br>

 

[Dreamboat]

A macro seems to be the hard way. If you want a macro, just have the macro insert this function, copy it down, copy the cells with the formula, then paste special as values, then delete the original column.<br><br>Here's the formula you want, assuming your data starts in A1:<br><br>=left(a1,4)&&quot; &quot;&mid(a1,5,3)<br><br>The first part pulls the first 4 characters reading left to right.<br><br>The second part puts the space in. There IS a space between the double-quote marks.<br><br>The third part starts at the 5th character in the number, and pulls the next three characters. This particular one will work with 7-digit numbers. To use it with 8-digit numbers, you'll need it to read mid(a1,5,4).<br><br>You could sort the file by that column first, which will put the 7-digit numbers at the top, so you can change the formula when you start hitting 8-digit numbers. <p> <br><a href=mailto: dreamboat@nni.com> dreamboat@nni.com</a><br><a href=

http://www.wedomanuals.com>

</a><br>