+ Reply to Thread
Results 1 to 10 of 10

Remove sign and subtract in the same formula

  1. #1
    Forum Contributor
    Join Date
    04-28-2016
    Location
    Stavanger, Norway
    MS-Off Ver
    365
    Posts
    111

    Remove sign and subtract in the same formula

    I use the following formula "=SUBSTITUTE('Worksheet1'!A1;"<";"")" to remove the less than sign from my data. Is it possible to add in subtract 0,00001 as well from the values after < has been removed?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Remove sign and subtract in the same formula

    Do you mean this?

    =SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001

    Or, if that won't work, this:

    =--SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,405

    Re: Remove sign and subtract in the same formula

    Try this.

    =SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001
    If posting code please use code tags, see here.

  4. #4
    Forum Contributor
    Join Date
    04-28-2016
    Location
    Stavanger, Norway
    MS-Off Ver
    365
    Posts
    111

    Re: Remove sign and subtract in the same formula

    Thanks that worked but it only needs to do it if its removing the less than sign. In this case it does it regardless

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,405

    Re: Remove sign and subtract in the same formula

    Try this.

    =IF(NOT(FIND("<";A1));A1;SUBSTITUTE(A1;"<";"")-0,0001)

  6. #6
    Forum Contributor
    Join Date
    04-28-2016
    Location
    Stavanger, Norway
    MS-Off Ver
    365
    Posts
    111

    Re: Remove sign and subtract in the same formula

    But now if the value doesnt have the less than sign I only get #value. So I would like the function to paste inn the value of it doesnt have <, if it has it I would like it to remove the < sign and subtract 0,00001 from it

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,405

    Re: Remove sign and subtract in the same formula

    Strange, when I try the formula with values that don't have the '<' it returns the value.

    Can you attach a workbook with sample data?

  8. #8
    Forum Contributor
    Join Date
    04-28-2016
    Location
    Stavanger, Norway
    MS-Off Ver
    365
    Posts
    111

    Re: Remove sign and subtract in the same formula

    So I add your formula but take the data from a different sheet "=IF(NOT(FIND("<";Sheet1!A1));Sheet1!A1;SUBSTITUTE(Sheet1!A1;"<";"")-0,0001)".
    Please see attached excel doc for example
    Attached Files Attached Files

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,405

    Re: Remove sign and subtract in the same formula

    Oops, I actually posted the wrong formula.

    Try this.

    =IF(ISERROR(FIND("<";Sheet1!A1));Sheet1!A1;SUBSTITUTE(Sheet1!A1;"<";"")-0,0001)
    Last edited by Norie; 10-06-2020 at 04:18 AM.

  10. #10
    Forum Contributor
    Join Date
    04-28-2016
    Location
    Stavanger, Norway
    MS-Off Ver
    365
    Posts
    111

    Re: Remove sign and subtract in the same formula

    That works, thank you!
    Last edited by mss90; 10-06-2020 at 04:40 AM.

+ 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. [SOLVED] Formula:add degree sign min sign and sec sign to a cell
    By khanaran in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-13-2020, 04:10 PM
  2. [SOLVED] Difference Formula or Hiding the - sign with a subtract formula.
    By JonWilf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2016, 01:37 PM
  3. Remove dollar sign in a formula
    By jolenec in forum Excel General
    Replies: 4
    Last Post: 03-14-2014, 10:02 PM
  4. [SOLVED] Remove - sign from columns
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2013, 10:05 AM
  5. Remove a sign from a number
    By Monica85 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-28-2013, 01:53 AM
  6. Formula to remove the comma sign at end of names
    By zicitron in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2013, 05:55 AM
  7. How to remove a space before the "=" sign and make the formula works properly?
    By Leosado in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2010, 03:05 PM

Tags for this Thread

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