Custom Format alphanumberic value with spaces
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 |
Custom Format alphanumberic value with spaces
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)... In article , "Bruce" wrote: 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 |
Custom Format alphanumberic value with spaces
On Sun, 27 Nov 2005 20:21:02 -0800, "Bruce"
wrote: 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 |
All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com