Adding Dashes to a 25 digit alphanumeric cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:

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



--
Regards,

Peo Sjoblom
 
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
Click to expand...

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)
 
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:
=LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,5)&"-"&MID(A1,16,5)&"-"&RIGHT(A1,5)If he wishes to do so, he could paste special values over the original codesif he no longer wants those.--David Biddulph"Kevin B" <[email protected]> wrote in messageSelect your numeric range and right click to activate the short cut menu.> SElect Format Cells and click the Number Tab. Scroll down the Categorypanel> and select Custom and then place the following mash in the Type field:>> #####-#####-#####-#####-#####> --> Kevin Backmann>>> "Nick" wrote:>>> I have a column of 30-5k consisting of a 25 digit alphanumeric code. Ineed>> 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
 
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
 
Back
Top