Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Inserting a Character into a String (In a Cell)

  1. #1
    Join Date
    14th September 2004
    Location
    Sioux City IA
    Posts
    121

    Inserting a Character into a String (In a Cell)

    Hello people,
    Slight problem with a Worksheet that I've inhereted,
    All the Time values in the cells have no ":" so I'm seeing times like (All in Military time) 700 of 2200
    How do I put a Colon in the string with VBA,
    I know about using the LEN to see where I would need to put it.. but thats as far as I can go..
    Any Advice will be to my sincearest appreciation..
    Thank you

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,433

    Re: Inserting a Character into a String (In a Cell)

    If you just want to keep it as a string rather than convert it to Excel time use
    =LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th September 2004
    Location
    Sioux City IA
    Posts
    121

    Re: Inserting a Character into a String (In a Cell)

    What about VBA?
    can I do a code for that?

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Inserting a Character into a String (In a Cell)

    VBA version of Derk's formula
    VB:
    Sub AddColon() 
        Dim rngTemp As Range 
         
        For Each rngTemp In Selection.Cells 
            rngTemp = Left(rngTemp, Len(rngTemp) - 2) & ":" & Right(rngTemp, 2) 
        Next 
    End Sub 
    
    

    Cheers
    Andy


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 6
    Last Post: August 4th, 2011, 05:19
  2. Inserting Character Codes
    By mebellus in forum EXCEL HELP
    Replies: 3
    Last Post: March 17th, 2007, 12:16
  3. Analyzing a string character by character
    By Alex Feature in forum EXCEL HELP
    Replies: 1
    Last Post: May 23rd, 2005, 23:32
  4. Replies: 6
    Last Post: July 19th, 2004, 10:47
  5. Inserting a Character with VBA code...
    By Michael Avidan in forum EXCEL HELP
    Replies: 5
    Last Post: January 30th, 2004, 23:06

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno