MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

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

Custom Formatting!

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,


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

=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.



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

That's better... [NT}

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 :


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

Is this what you had in mind ?

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

Re: That's better if

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