CUSTOM TEXT FORMATTING???

My cell has 8 mixed characters of numbers and letters.  I 
need to have a hyphen "-" after the first 4 characters in 
a column.  Can I make a custom text format so no matter 
what I enter a hyphen is automatically inserted?  How do I 
do that?

JLC
0
anonymous (74722)
4/5/2004 3:51:29 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
380 Views

Similar Articles

[PageSpeed] 55

Hi
this could only be done with VBA, using an event procedure (as you have
text and numbers in your cell). e.g. put the following code in your
worksheet module (not in a standard module):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
    With Target
        If len(.Value) = 8 Then
             .value = left(.value,4) & "-" & right(.value,4)
        End If
    End With
CleanUp:
    Application.EnableEvents = True
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany


JLC wrote:
> My cell has 8 mixed characters of numbers and letters.  I
> need to have a hyphen "-" after the first 4 characters in
> a column.  Can I make a custom text format so no matter
> what I enter a hyphen is automatically inserted?  How do I
> do that?
>
> JLC

0
frank.kabel (11126)
4/5/2004 4:05:26 PM
Reply:

Similar Artilces:

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Excel Text Function
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...

Outlook 2003
Is there a way to force all incoming email to be received as plain text in OL-2003? TIA -- howard How about: "Tools | Options | E-Mail Options | Read all standard mail in plain text"? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Howard Cross" <no-spam@No-Spam.com> wrote in message ...

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...

To write living will do I need special format
I just need to change and update a living trust You should consult someone with the appropriate legal knowledge in the jurisdiction in which you are domiciled. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "belladonna" <belladonna@discussions.microsoft.com> wrote in message news:F25A64CB-355F-44E9-A005-16AA61DA15A7@microsoft.com... > I just need to change and update a living trust ...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

Article published by Microsoft reg. 'Event' custom entity
Recently, I found a great article published by Microsoft that contains a sample code on how to create a custom entity, event. I thought that I bookmarked it but cannot find it. Has anyone seen it and can provide a hyperlink? I will really appreciate it. http://msdn2.microsoft.com/en-us/library/aa682866.aspx you'll probably find it in the above link "mkatsev" wrote: > Recently, I found a great article published by Microsoft that contains a > sample code on how to create a custom entity, event. I thought that I > bookmarked it but cannot find it. Has anyone seen...

Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...

VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...

conditional format 04-15-10
Hi, I want the color of the text in cell B1 to change depending on the value in cell A1 How can this be done? I know I can do it with conditionale format for the cell itself but not for another one. Thanks JP Try this. Assume you want B1 text to change colour when the value in A1 is 5, select B1 and go to conditional formatting. In the condition, select 'Formula is' then in the empty space, type =A1=5 choose the font colour that you want. "Jean-Paul" wrote: > Hi, > > I want the color of the text in cell B1 to change depending on th...

Special format
How can I change the format from numerical to English?? e.g.: 420 to Four Hundred and Twenty Only It is called typing - UNBELIEVABLE or SEARCH and REPLACE but MS Office 2002 Chin support numerical to chinese: 123456789 -----> = &#19968;&#20740;&#20108;&#21315;&#19977;&#30334;&#22235;&#21313;&#20116;&= #33836;&#20845;&#21315;&#19971;&#30334;&#20843;&#21313;&#20061; =3D.=3D" Can anyone help~~ >-----Original Message----- >It is called typing - UNBELIEVABLE > >or SEARCH and REPLACE > > >. > ...

reply formatting in Outlook 2010
I set up my normal.email.dotm file to have 12 pts below each paragraph, which helps formatting issues when sending emails to people using gmail and other programs that interpret paragraph breaks differently than Outlook. That works just fine with new emails. However, when I reply to an email, even an html email, the formatting is not applied. I can fix this by choosing Format Text and then setting the spacing as per above, but this is a pain in the neck. Why don't reply emails use the same template? Is there a way to template replies so i can use the same formatting? thanks in advance, ...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Exchange 2K: Controlling incoming email formats....
Question... AFAIK (and correct me if I am wrong), Exchange natively only lets you control message formats for messages that are downloaded to POP\IMAP clients. For security reasons, I would like Exchange to convert all incoming\outgoing email from\to external domains, as well as INTRA-domain email , to plain text format. In other words, I do not want to see anymore html email on my server going in and out, just plain text. Is there a setting for this for Exchange to handle this natively? If not, what (if any) 3rd party solutions are out there for this? Thanks, George Here's the Re...

Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

Formatting in the formula bar
When i type in a number into a cell in my worksheet, say: 42.99 Excel automatically rounds it to 43. Which is what i want and what i set it up to do. However, the number in the formula bar also rounds to 43. Normally i thought the formula bar stayed at 42.99 and only the spreadsheet cell rounds to 43. I am using Excel 2000. Please help asap as i need the formula bar to stay at 42.99 so i remember what the original number was!!! Hi maybe you have checked 'Tools - Options - Calculation - Precision as displayed' -- Regards Frank Kabel Frankfurt, Germany Beccy wrote: > When ...

Help replacing text with Yes or No
I have a field formated as general. The field contains either 1 or is left blank. If the field has a 1 I want to replace it with Yes and if the field is blank I want to replace it with No. any help is appreciated. -- Jerry Save your data and use a copy for this exercize........... Assuming your data in Column A, put this in B1 and copy down........ =IF(A1=1,"Yes","No") Then highlight the column and do Copy > PasteSpecial > Values to get rid of the formulas..........then delete column A if you wish....... Vaya con Dios, Chuck, CABGx3 "Jerry Arnone, ...

Form Formatting
I have a subform (datasheet view) in a form where I want to make one column a different color. I can go into Design view and change the color but it doesn't change in the form view. This form was made ages ago with an automatic format and now I can't get away from the automatic format. Help! "DSmith" <donna@DONTSPAMresxrn.com> wrote in message news:eQARsjI6KHA.4508@TK2MSFTNGP06.phx.gbl... >I have a subform (datasheet view) in a form where I want to make one column >a different color. I can go into Design view and change the color but it >doe...

Conditional format a column
Hello, I am comparing numbers in column b and column c. In column H, I've written an if then statement stating something to the effect that the "B is greater than C" or "B is less than C" as the case may be. I'd like to format the cells in H with red coloring where B is less than C. I've been able to do it with a single cell using conditional formatting, but need help with a column of cells. Could someone give me directions? Thanks in advance, Ellen Ellen, Select your cells in column H to be formatted. Best not to select the entire column by click...

How do I change the default number format ?
The current number format is general. I would like to change it to accounting with no $ and 2 decimals. I can't seem to find how. I'd appreciate any help that anyone can give me. Thank you. Hi. You would want to change the definition of the "Normal" style. From the menu: Format | Style... And modify the "Normal" style HTH -- Dana DeLouis Win XP & Office 2003 "LarryH" <LarryH@discussions.microsoft.com> wrote in message news:EA38F73B-D938-4EB7-804F-6716C292E3E3@microsoft.com... > The current number format is general. I would like to...

Change the default format of the query design view
When I use the query design view, I have to increase the size of the table window (from which I'm selecting fields) sideways and vertically to see the field names more clearly and that also means moving the criteria grid further down the page to make room. Is there a way to change the default table window size and default grid position so that I dont' have to do this every time? Thanks, Pat Pat When you find it, let the newsgroup know! You are (unfortunately) not the first person to wish there was a setting...<g> Regards Jeff Boyce Microsoft Office/Access MVP "...

Can't open html format email in outlook xp
Dear all , I can't open and create html format emails in outlook XP . I think I lost some html and RTF components , I tried to reinstall the outlook xp and selected all the related options , but still can't work. Could anyone help me to solve the problem? Thank in advance Stanley ...