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,
Russell
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
=======
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 :
=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
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...
Aladin
==========
