+ Reply to Thread
Results 1 to 5 of 5

Removing a dash

  1. #1
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Removing a dash

    I would like cell A2 to look at cell A3 and repeat the same value but without the dash. A3 may be the result of a formula if that matters.

    For example, A3 will say 1234-56 which would make A2 say 123456. The only 2 formats that would come into play are 1234-56 and 1234-567. I'm still learning about the difference in how text and numbers are treated in different formulas. I believe these would all be text. Out of curiosity, would the output be text then too?

    Edit - If I use MID I can get the results I need by being specific, but when I try to add SEARCH to it to find the dash it doesn't word for me.

    Another edit (because I do try to figure it out on my own, I don't just post).
    I can use =MID(A3,1,4)&RIGHT(A3,LEN(A3)-4) which works!!! Yay me for figuring it out on my own.

    Thank you.
    Last edited by The Phil; 03-15-2010 at 07:22 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing a dash

    How about this:

    =SUBSTITUTE(A3, "-", "")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Removing a dash

    Quote Originally Posted by JBeaucaire View Post
    How about this:

    =SUBSTITUTE(A3, "-", "")
    Works better than my formula

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Removing a dash

    Assuming there are no formulas in your range that require subtraction, you could also use Ctrl+H, search for -, leave replace with blank, and replace all. No helper column that way.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing a dash

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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