+ Reply to Thread
Results 1 to 6 of 6

Is it possible to insert a hyphen in text by formula?

  1. #1
    Registered User
    Join Date
    12-12-2008
    Location
    Memphis, TN
    Posts
    3

    Is it possible to insert a hyphen in text by formula?

    I have some data in the form of text w/ 8 letters. I'd like to insert a hyphen after the third character. Is this possible using a formula to populate an entire field? Example:

    Current format: ABC01234

    Desired format: ABC-01234
    Last edited by Brahma72472; 12-30-2008 at 12:18 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try:

    =REPLACE(A1,4,0,"-")

    where A1 contains original
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    12-12-2008
    Location
    Memphis, TN
    Posts
    3
    Wow...u guys/gals are awesome. The replace function worked brilliantly. Thanks for the help!!!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  5. #5
    Registered User
    Join Date
    01-11-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Is it possible to insert a hyphen in text by formula?

    Is it possible to insert that "=replace(..)" formula within an IF formula? I sometimes have spreadsheets that will have values in column A, but only in some of the rows. I'd like to be able to say If there's a value in column A, insert a '-' between characters 3 and 4. I like working with formulas where possible and avoiding macros.

    Thanks!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Is it possible to insert a hyphen in text by formula?

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1