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:

#####-#####-#####-#####-#####
--
Kevin Backmann


"Nick" wrote:

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

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

"Kevin B" wrote:

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:

#####-#####-#####-#####-#####
--
Kevin Backmann


"Nick" wrote:

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

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




"Kevin B" wrote in message
...
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:

#####-#####-#####-#####-#####
--
Kevin Backmann


"Nick" wrote:

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

On Mon, 27 Aug 2007 08:14:00 -0700, Nick
wrote:

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

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)



--
Regards,

Peo Sjoblom



"Nick" wrote in message
...
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

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" wrote in ... Select 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

"Peo Sjoblom" wrote:

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)



--
Regards,

Peo Sjoblom



"Nick" wrote in message
...
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