Inputing a dash in UPC

[JCG82]

I have 458 line's of UPC code. The UPC numbers have all the correct digits but they have a space after the first and last letter.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cjgough%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> 0 49000-02820 1<o></o>

I need to add a dash between the first and second letter and the second to last letter instead of the blank space. I'm hand keying it at the moment. Does anyone have any good suggestions to speed this process up a little ?

 

[MrKowz]

One quick and dirty solution is if you have everything in column A, insert the following formulas into B1 and C1, then copy down.

B1 =REPLACE(A1,FIND(" ",A1),1,"-")
C1 =REPLACE(B1,FIND(" ",B1),1,"-")
Nothing elegant, but it works!

 

[EugeneCarter]

One quick and dirty solution is if you have everything in column A, insert the following formulas into B1 and C1, then copy down.

B1 =REPLACE(A1,FIND(" ",A1),1,"-")
C1 =REPLACE(B1,FIND(" ",B1),1,"-")
Nothing elegant, but it works!

Can't you just select the range and do find/replace as well?

 

[JCG82]

Works like magic.

I owe you!

Thanks for saving me 2 hours lol

 

[MrKowz]

Can't you just select the range and do find/replace as well?

Yep, you could do that as well!

 

[pedy]

I would like to do the opposite (remove the dashes) but I want to do it in a date type formating.

Ex.: 07-02-02 would display: 070202

Pedy

 

[MrKowz]

Assuming your data is in A1, and the dates are in Date Format:

Put the following formula in B1:

=IF(LEN(MONTH(A1))<2,"0"&MONTH(A1),MONTH(A1))&IF(LEN(DAY(A1))<2,"0"&DAY(A1),DAY(A1))&RIGHT(YEAR(A1),2)

 

[pedy]

That works GREAT! THANKS !!!

But I would also need the year to display 2 digits for a total of 6 digits.

Ex 01-24-08 would display: 012408

Thanks again !

Pedy

 

[MrKowz]

It will. It forces excel to pick the right 2 numbers. whether they be 89 or 00.

 

[pedy]

It does indeed but I forgot to mention that I modified the formula to fit the yy-mm-dd format as follows:

=IF(LEN(YEAR(A19))<2,"0"&YEAR(A19),YEAR(A19))&IF(LEN(MONTH(A19))<2,"0"&MONTH(A19),MONTH(A19))&RIGHT(DAY(A19),2)

...and for a 07-04-12 it displays 20070412

Note that the cell format number is Date (01-03-14) and I am using 2007...

Pedy