How to insert hyphen "-" in between two numbers

Discussion in 'Microsoft Excel Worksheet Functions' started by Big Cat, Jul 24, 2006.

  1. Big Cat

    Big Cat Guest

    Hi All:

    I was wondering if one of you Excel guru's out there could coach me
    through this challenge I have. I run an extract report from our
    business system into Excel and I need to have a particular column of
    data changed as follows:

    From: 06001 To: 06-001

    Is there a formula of some sort that I could write to handle inserting
    the hyphen "-" in between the 2nd and 3rd character of my data column
    to accomplish this?

    Please help.

    Thanks,

    Big Cat
     
    Big Cat, Jul 24, 2006
    #1
    1. Advertisements

  2. Big Cat

    Guest Guest

    Hi,

    As long as it's always in between second and third characters this should
    work:

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

    where A1 contains the original text
     
    Guest, Jul 24, 2006
    #2
    1. Advertisements

  3. On 23 Jul 2006 22:52:22 -0700, "Big Cat" <> wrote:

    >Hi All:
    >
    >I was wondering if one of you Excel guru's out there could coach me
    >through this challenge I have. I run an extract report from our
    >business system into Excel and I need to have a particular column of
    >data changed as follows:
    >
    >From: 06001 To: 06-001
    >
    >Is there a formula of some sort that I could write to handle inserting
    >the hyphen "-" in between the 2nd and 3rd character of my data column
    >to accomplish this?
    >
    >Please help.
    >
    >Thanks,
    >
    >Big Cat


    1. You could custom format the column:
    Format/Cells/Number/Custom Type: 00-000

    2. You could use the formula:

    =TEXT(A1,"00-000")


    --ron
     
    Ron Rosenfeld, Jul 24, 2006
    #3
  4. Big Cat

    Big Cat Guest

    Ron,

    Thanks for the help. I found it easiest to go with option 1 to solve
    my problem immediately. If I wanted to use option 2 (formula), where
    would I insert the formula in the list?

    Please advise.

    Thanks,

    Big Cat

    Ron Rosenfeld wrote:
    > On 23 Jul 2006 22:52:22 -0700, "Big Cat" <> wrote:
    >
    > >Hi All:
    > >
    > >I was wondering if one of you Excel guru's out there could coach me
    > >through this challenge I have. I run an extract report from our
    > >business system into Excel and I need to have a particular column of
    > >data changed as follows:
    > >
    > >From: 06001 To: 06-001
    > >
    > >Is there a formula of some sort that I could write to handle inserting
    > >the hyphen "-" in between the 2nd and 3rd character of my data column
    > >to accomplish this?
    > >
    > >Please help.
    > >
    > >Thanks,
    > >
    > >Big Cat

    >
    > 1. You could custom format the column:
    > Format/Cells/Number/Custom Type: 00-000
    >
    > 2. You could use the formula:
    >
    > =TEXT(A1,"00-000")
    >
    >
    > --ron
     
    Big Cat, Aug 6, 2006
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Larry

    adding a hyphen

    Larry, Jun 28, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    536
  2. Guest

    ...I want to display a hyphen instead of zero...

    Guest, Aug 1, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    190
    Guest
    Aug 1, 2005
  3. tcjay

    Truncating text before a hyphen

    tcjay, Sep 6, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    190
    tcjay
    Sep 6, 2005
  4. Guest

    Entering a hyphen into a cell

    Guest, Mar 30, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    136
    Guest
    Mar 30, 2006
  5. Jen

    I need a fx for counting numbers between numbers (between 18 - 59

    Jen, Feb 24, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    352
    Shane Devenshire
    Feb 24, 2009
Loading...

Share This Page