+ Reply to Thread
Results 1 to 6 of 6

Question on Formula

  1. 07-16-2014, 05:11 PM #1
    peteca
    peteca is offline
    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?
    Reply With QuoteRegister To Reply
  2. 07-16-2014, 05:28 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Question on Formula 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 07-16-2014, 05:30 PM #3
    Jack7774
    Jack7774 is offline
    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.
    Reply With QuoteRegister To Reply
  4. 07-16-2014, 05:31 PM #4
    Jack7774
    Jack7774 is offline
    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 .
    Reply With QuoteRegister To Reply
  5. 07-16-2014, 05:32 PM #5
    gak67
    gak67 is offline
    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.
    Reply With QuoteRegister To Reply
  6. 07-17-2014, 11:57 AM #6
    peteca
    peteca is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2007 : Basic formula question for Excel 2007 --copying same formula to apply to whole column
    By ciorg in forum Excel General
    Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  2. format question when in a formula and nested VLOOKUP, OFFSET and HLOOKUP formula
    By nutrition_man2 in forum Excel General
    Replies: 2
    Last Post: 10-08-2009, 09:51 PM
  3. Concatenate Question - How do I copy and paste the formula's result - not the formula
    By Rokketman in forum Excel General
    Replies: 1
    Last Post: 05-20-2009, 08:15 AM
  4. Concatenate Question - How do I copy and paste the formula's result - not the formula
    By Rokketman in forum Excel General
    Replies: 1
    Last Post: 05-20-2009, 07:56 AM
  5. [SOLVED] Newbie Question - Subtraction Formula Question
    By [email protected] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1