Closed Thread
Results 1 to 5 of 5

How to Substitute a Singular/Plural word to nothing in an Alphanumeric Text..?

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    How to Substitute a Singular/Plural word to nothing in an Alphanumeric Text..?

    How to Substitute a Singular/Plural word to nothing in an Alphanumeric Text..?

    I have a certain formula in which I have to Replace/Substitue same words but they occur in singular or plurals then how can that be done by using wild characters?

    Lets say I have a cell A1= 1 Hour or 2 Hours or 1 Day or 2 Days..
    I need to convert this text into minutes 1.e. 1 Hour would be 60,120 but if its 1 Day then 24*60 or 2 Days then 24*2*60 etc..

    I want to have acomapct formula rather than use a lot of IF conditions..if possible..
    Last edited by e4excel; 04-30-2010 at 10:00 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How to Substitute a Singular/Plural word to nothing in an Alphanumeric Text..?

    Try this:

    =LEFT(A1,FIND(" ",A1))*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"s","")," Hour",1)," Day",24)

    Cell must be format as:

    NUMBER SPACE TEXT MENTIONED ABOVE

    ie 23 Hours

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to Substitute a Singular/Plural word to nothing in an Alphanumeric Text..?

    or like this:

    =IF(ISERROR(FIND("hour",A1)),LEFT(A1,FIND(" ",A1)-1)*60*24,LEFT(A1,FIND(" ",A1)-1)*60)

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: How to Substitute a Singular/Plural word to nothing in an Alphanumeric Text..?

    Dear Zbor,

    I am not sure whether that works or not though you have considered both the options of HOUR/HOURS AND DAY/DAYS..

    Please mentione the format as I have not understood as to what the format needs to be:

    I am attaching a file for your reference so that you can Edit in the same and please Add comments wherever neccessary...

    Dear Tevlyn,

    I think this is what I was looking for just with a minor change at the last minute of Capitalizing the "H" in your formula it worked..

    I am goingto mark this thread as solved however need to figure about the Format from Zbor..

    Thanks a lot both of you..!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-01-2010
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2007
    Posts
    2

    How to make sum between x hour,y min?

    Hy guys,

    I have a table with this data and i need to make a weekly total.
    Can help me?

    01-Sep-10 02-Sep-10 03-Sep-10 Weekly total
    44h, 31min 50h, 48min 38h, 52min ?
    29h, 10min 38h, 41min 35h, 12min ?
    38h, 12min 39h, 53min 34h, 18min ?
    33h, 24min 36h, 12min 35h, 6min
    19h, 59min 22h, 17min 20h, 53min
    24h, 22min 26h, 46min 23h, 22min

Closed 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