Forum Discussion

Gows94's avatar
Gows94
Copper Contributor
Jul 21, 2021

Replace option in excel

I want to find a single cell data and replace with values in three cells
Eg : Finding 4
Replacing with 0.1 in one cell, 0.2 in another, 0.3 in another.

Could anyone help with this please

Thanks 🙏
excel

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 21, 2021
    Maybe you could give a bit more information about what you are trying to do and how the data is set up (it would be great if you attached a sample sheet without personal info). But I will guess at the situation. So if you have a column of data and every time you have a "4" you want to "replace" it with a value counting in tenths. But first of all, in excel you don't "replace" data but "use" data to create a new set of resulting data so lets say you have the following data in Col A and then in Col B you have the same data but with each "4" replaced with a count. If you must actually "replace" data you need to use a macro or you can use Find and Replace functionalities (but I will skip that for now). So to get the following:
    Col A Col B
    2 2
    4 0.1
    8 8
    4 0.2
    4 0.3
    1 1
    2 2
    4 0.4

    You can use the following formula in B1 and fill down
    =IF(A1=4, 0.1*COUNTIF($A$1:$A1,4), A1)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 21, 2021

    Gows94 

    Suitable for Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Starter 2010

     

    Find or replace text and numbers on a worksheet

    Use the Find and Replace features in Excel to search for something in your workbook, such as a particular number or text string. You can either locate the search item for reference, or you can replace it with something else. You can include wildcard characters such as question marks, tildes, and asterisks, or numbers in your search terms. You can search by rows and columns, search within comments or values, and search within worksheets or entire workbooks.

    More information in the microsoft link above

     

    🙂

    With formula:

    REPLACE, REPLACEB functions

    This article describes the formula syntax and usage of the REPLACE and REPLACEB  function in Microsoft Excel.

     

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Resources