 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / August 2007Adding Dashes to a 25 digit alphanumeric cell
I have a column of 30-5k consisting of a 25 digit alphanumeric code. I need to reformat these to have dashes every 5 characters. example: XXXXXXXXXXXXXXXXXXXXXXXXXX to XXXXX-XXXXX-XXXXX-XXXXX-XXXXX Is there a custom format string that I can use to do this automatically? My right arrow key is getting worn down here, :P Thanks in advance, Nick Select your numeric range and right click to activate the short cut menu. SElect Format Cells and click the Number Tab. Scroll down the Category panel and select Custom and then place the following mash in the Type field: #####-#####-#####-#####-#####  SignatureKevin Backmann > I have a column of 30-5k consisting of a 25 digit alphanumeric code. I need > to reformat these to have dashes every 5 characters. example: [quoted text clipped - 8 lines] > > Nick Thanks for the quick reply, Kevin. I tried that string before I posted to this forum, but it does not work. Maybe since it's alphanumeric? Any other ideas, or maybe I'm not doing something right? Nick > Select your numeric range and right click to activate the short cut menu. > SElect Format Cells and click the Number Tab. Scroll down the Category panel [quoted text clipped - 14 lines] > > > > Nick That will not work, Excel can only display 15 digits the rest will be truncated to zero The OP obviously is using text values for this and they cannot be formatted the same way a number can. SignatureRegards, Peo Sjoblom > Select your numeric range and right click to activate the short cut menu. > SElect Format Cells and click the Number Tab. Scroll down the Category [quoted text clipped - 17 lines] >> >> Nick That works for numbers, but the OP said "alphanumeric" (and of course if they'd been numbers the last 10 digits would have been zeroes as Excel can cope with only 15 significant digits for numbers). In which case, I think he needs a helper column: >I have a column of 30-5k consisting of a 25 digit alphanumeric code. I need >to reformat these to have dashes every 5 characters. example: [quoted text clipped - 8 lines] > >Nick Use a formula of the type: =LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,5) ... &RIGHT(A1,5) Fill down as far as required. Then select the column and do Edit/Paste Special/Values You can then delete (or hide) the originals --ron You cannot format text the same ways you format a number =LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,5)&"-"&MID(A1,16,5)&"-"&MID(A1,21,5) SignatureRegards, Peo Sjoblom >I have a column of 30-5k consisting of a 25 digit alphanumeric code. I >need [quoted text clipped - 10 lines] > > Nick Thank you all who replied to this thread! Problem solved, using the formula below and copy-pasting the values back into column A! I love this forum!! Nick > You cannot format text the same ways you format a number > [quoted text clipped - 14 lines] > > > > Nick |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.