+ Reply to Thread
Results 1 to 12 of 12

If a cell contains a specific word and a number, make the number negative

  1. 01-08-2017, 05:37 AM #1
    Andrew Watson
    Andrew Watson is offline
    Registered User
    Join Date
    01-08-2017
    Location
    Oxford, England
    MS-Off Ver
    Excel
    Posts
    5

    If a cell contains a specific word and a number, make the number negative

    Hi there

    I am stuck! Really appreciate some help.

    I have a column of entries as below:

    Left 5
    Left 10
    Right 5
    Left 20
    Right 5

    I want this to be converted so that cells that contain the word "left" turn the value negative: eg.

    -5
    -10
    5
    -20
    5

    Can anyone help???
    Reply With QuoteRegister To Reply
  2. 01-08-2017, 05:39 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,275

    Re: If a cell contains a specific word and a number, make the number negative

    So you want to return just the number part of the cell, and make it negative if the word was left?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-08-2017, 05:41 AM #3
    Andrew Watson
    Andrew Watson is offline
    Registered User
    Join Date
    01-08-2017
    Location
    Oxford, England
    MS-Off Ver
    Excel
    Posts
    5

    Re: If a cell contains a specific word and a number, make the number negative

    Hi Ali,

    Yes that's exactly it.

    Is there a way of doing this without having to separate out the words and numbers manually?
    Reply With QuoteRegister To Reply
  4. 01-08-2017, 05:42 AM #4
    Andrew Watson
    Andrew Watson is offline
    Registered User
    Join Date
    01-08-2017
    Location
    Oxford, England
    MS-Off Ver
    Excel
    Posts
    5

    Re: If a cell contains a specific word and a number, make the number negative

    PS thanks for your quick reply!
    Reply With QuoteRegister To Reply
  5. 01-08-2017, 05:48 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,275

    Re: If a cell contains a specific word and a number, make the number negative

    Try this:

    =--IF(ISERROR(FIND("Left",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),-RIGHT(A1,LEN(A1)-FIND(" ",A1)))
    Reply With QuoteRegister To Reply
  6. 01-08-2017, 05:58 AM #6
    Andrew Watson
    Andrew Watson is offline
    Registered User
    Join Date
    01-08-2017
    Location
    Oxford, England
    MS-Off Ver
    Excel
    Posts
    5

    Re: If a cell contains a specific word and a number, make the number negative

    Hi Ali,

    Wow! Just wow. I don't know why it works, but it works.

    Many thanks

    Andy
    Reply With QuoteRegister To Reply
  7. 01-08-2017, 05:59 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,275

    Re: If a cell contains a specific word and a number, make the number negative

    I'll explain it if you like ...
    Reply With QuoteRegister To Reply
  8. 01-08-2017, 06:04 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,275

    Re: If a cell contains a specific word and a number, make the number negative

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  9. 01-08-2017, 06:05 AM #9
    Andrew Watson
    Andrew Watson is offline
    Registered User
    Join Date
    01-08-2017
    Location
    Oxford, England
    MS-Off Ver
    Excel
    Posts
    5

    Re: If a cell contains a specific word and a number, make the number negative

    Hi Ali,

    If you're up for explaining, I'm up for learning!

    Especially if I need to tailor it in future. I have another list which also has values such as Left >30, which I need to make - >30, ideally - if not I can probably do a manual workaround.
    Reply With QuoteRegister To Reply
  10. 01-08-2017, 06:10 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,275

    Re: If a cell contains a specific word and a number, make the number negative

    OK. Here goes!

    =--IF(ISERROR(FIND("Left",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),-RIGHT(A1,LEN(A1)-FIND(" ",A1)))

    -- forces the formula to return a real number and not text.

    ISERROR(FIND("Left",A1)) checks for the non-existence of "Left" in the cell ...

    RIGHT(A1,LEN(A1)-FIND(" ",A1))

    and returns the result of this bit if there is an error. In other words, if cell A1 does NOT contain "Left", it returns the number on the right.

    If there isn't an error, it returns this:

    -RIGHT(A1,LEN(A1)-FIND(" ",A1))

    which just makes the number returned negative.

    RIGHT(A1,LEN(A1)-FIND(" ",A1)) is returning the number of characters from the right edge of the cell that are the total number of characters in the cell - LEN(A1) - minus the number of characters up to and including the first " " (space).
    Reply With QuoteRegister To Reply
  11. 01-08-2017, 06:34 AM #11
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,958

    Re: If a cell contains a specific word and a number, make the number negative

    Another way ..

    =--SUBSTITUTE(SUBSTITUTE(TRIM(A1),"Left","-"),"Right","")
    Reply With QuoteRegister To Reply
  12. 01-08-2017, 10:13 AM #12
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell contains a specific word and a number, make the number negative

    Here's another one...

    Data Range
    A
    B
    1
    Left 5
    -5
    2
    Left 10
    -10
    3
    Right 5
    5
    4
    Left 20
    -20
    5
    Right 5
    5
    6
    ------
    ------


    This formula entered in B1 and copied down:

    =MID(A1,FIND(" ",A1),5)*IF(LEFT(A1,4)="Left",-1,1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    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. making a number positive or negative based on a word in another cell?
    By dbwoods11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2013, 04:19 AM
  2. [SOLVED] Extracting word for number of specific lenth to other cell
    By parajf in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-11-2012, 09:51 PM
  3. A+B = C, but if A is a negative number, then make it 0.
    By PowerSchoolDude in forum Excel General
    Replies: 2
    Last Post: 10-27-2009, 02:24 PM
  4. make a negative number zero
    By Decopk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2005, 04:25 PM
  5. [SOLVED] How to make the cell or font color red if the number is negative?
    By Bob T in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 09-06-2005, 07:05 PM
  6. make negative number value to zero
    By india0693 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 06:05 PM
  7. [SOLVED] make cell entries a negative number
    By gls858 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-01-2005, 06:05 PM
  8. [SOLVED] If Cell Not Number Than Specific Word?
    By JK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2005, 03:06 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