Custom Format alphanumberic value with spaces

[Guest]

This ones more formatting related than formula but I didn't know where else
to put it.

Say I enter the value c7454951000000cgvxx I want to pre-format it
with predefined '-' after a certain number of characters.....

I intend the formatting will change my value to;
C-745-4951-000-000-CGV-XX

I have tried the following custom format but it did not work..
#-###-####-###-###-###-##

Does anyone have any suggestions?

Bruce

 

[JE McGimpsey]

Mixed alphanumerics are text, and custom (number) formats won't do
anything with text. You could use an event macro to insert the dashes
(but that will change the actual value, not just the display), or you
can use another cell, e.g.:

=LEFT(A1,1)&"-"&MID(A1,2,3)&"-"&MID(A1,5,4)...

 

[Ron Rosenfeld]

c7454951000000cgvxx

You cannot format a text string using number formatting. One method is to use
TEXT functions, as written by McGimpsey, to have the display appear the way you
describe.

If the letters are always the same, you could just enter the numeric portion,
and use the custom format:

C-000-0000-000-000-"CG-VXX"

If the letters vary, you could use an event macro to do the custom formatting.

You would enter the full string as in your initial post, and then have the
event macro separate out the number from the text, and construct a custom
format. The data stored would be just the number.

You would have one custom format for each variation in alpha characters. Since
the number of custom formats is limited depending on the amount of memory, you
may wind up with too many formats.


--ron