+ Reply to Thread
Results 1 to 6 of 6

Question on Formula

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    seattle wa
    MS-Off Ver
    2013
    Posts
    4

    Question on Formula

    I am trying to extract information to the left side of ":", which is contained in all cells I'm trying to get this information from. An example of this would be "0004: BlahBlah". What I want to do is pull the 0004, which I have done using =LEFT(J2,FIND(":",J2)-1). But I'm having trouble removing all the 0's leaving just the number 4.

    I want to use this same formula on other cells which would contain, for example, "45: blablah" or "8011: blahblah" without removing the 45 or 8011. Is there a way to accomplish this?

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Question on Formula

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor Jack7774's Avatar
    Join Date
    08-31-2012
    Location
    Georgia, US
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Question on Formula

    After you use the fomula to get the 0004 do this. Put a 1 in a cell. Copy it. Then highlight the 0004 cell. Right click then select paste special and select multiply and values then click ok. It should change the 0004 to a number 4 rather than text of 0004.
    Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.

  4. #4
    Forum Contributor Jack7774's Avatar
    Join Date
    08-31-2012
    Location
    Georgia, US
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Question on Formula

    Quote Originally Posted by mehmetcik View Post
    Please Login or Register  to view this content.
    Nice. I didnt' think to add the *1 to the formula. I didn't know that would work .

  5. #5
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Question on Formula

    You should change your title as it doesn't meet the forum rules. An alternative to mehmetcik's formula would be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  6. #6
    Registered User
    Join Date
    07-16-2014
    Location
    seattle wa
    MS-Off Ver
    2013
    Posts
    4

    Re: Question on Formula

    Thanks guys. That worked perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  2. Replies: 2
    Last Post: 10-08-2009, 09:51 PM
  3. Replies: 1
    Last Post: 05-20-2009, 08:15 AM
  4. Replies: 1
    Last Post: 05-20-2009, 07:56 AM
  5. [SOLVED] Newbie Question - Subtraction Formula Question
    By admiral_victory@iol.ie in forum Excel General
    Replies: 3
    Last Post: 05-05-2006, 12:55 PM

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