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 ?

 

[Jonmo1]

=TEXT(A1,"mmddyy")

 

[MrKowz]

Ahh, i see what you need. Give this a shot:

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

Edit: And Jomno1 steps up to the plate and SLAMS a long one! It's going, going, GONE! Grand Slam for Jonmo1!

 

[pedy]

oh yes that did it !

Thanks so much !

Pedy

 

[Jonmo1]

Haven't played baseball in quite a while...

 

[EugeneCarter]

I think he'd prefer:
=TEXT(A1,"yymmdd")

 

[pedy]

Sorry for not getting back on this.

=TEXT(A1,"yymmdd") works just fine also!

Thanks so much for all your help!


I have another question, not sure that I should post it right here but here goes:

I have 9 cells in a row that I want to count the amount of times "true" appears. This "true is a result of a formula that validates if 2 numbers in 2 different cells are the same (rounded out to a 4 digit decimal). This is the formula: =IF(ROUND(C13,4)=ROUND(L13,4),"true","false")

Now, the return of this is either true or false. As I said I want to count the amount of true returns however, the true or false that are returned in these cells are in fact the formula so I can't use this type of formula : =COUNTIF(U6:AC6,"TRUE")

I tried using this formula but it still doesn't work: =COUNTIF(U6:AC6,T("TRUE"))

Any help would be appreciated

Thanks

Pedy

 

[Jonmo1]

First, I would change this formula
=IF(ROUND(C13,4)=ROUND(L13,4),"true","false")

to
=ROUND(C13,4)=ROUND(L13,4)

that will return TRUE or FALSE, there's no need to say IF and return text strings "true" or "false"...

then the formula should work like this
=COUNTIF(U6:AC6,TRUE)

Hope that helps...

 

[pedy]

Yup !

That did the trick !!!

Thanks again