Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

changing existing numbers

Posted by Cheri I on January 17, 2002 1:38 PM

I have a price list sent to me by one of our suppliers that uses a 7 digit product code. I need to insert a hyphen after the first 2 digits in every one of the 9000 items in this price list. I know there is a simple way to do it but for the life of me, I can't remember how.

Custom Formatting!

Posted by Russell Hauf on January 17, 2002 1:48 PM

Select the cells that you want your hyphen in. Go to Format-Cells (or type CTRL+1) and in the Number tab, under Category, select Custom. Type in 00-00000

Hope this helps,

Russell

Re: changing existing numbers

Posted by Aladin Akyurek on January 17, 2002 1:51 PM

Cheri --

Since your product codes are all 7 digits,

insert a new column next to the column containing these codes, and
enter

=LEFT(A1,2)&"-"&MID(A1,3,5) [ assuming orig codes in A, otherwise ad just ]

then give a double click on the little black square of the cell that houses the above formula.

Aladin

=======

That's better... [NT}

Posted by Aladin Akyurek on January 17, 2002 1:52 PM

Re: changing existing numbers

Posted by Chris D on January 17, 2002 1:53 PM

not sure if this is the best way to do it, but, assuming your first 7-digit product code is in cell A1 :

=MID(+A1,1,2)&"-"&MID(+A1,3,5)

typed into cell B1 should work (then maybe copy/paste special/values the whole of column B)

Is this what you had in mind ?
Chris

Re: That's better if

Posted by Aladin Akyurek on January 17, 2002 1:58 PM

we are not going match them to a column of product codes with real hyphens in them...

Aladin

==========

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.