User Posts: Raiyan Zaman Adrey
0
How to Use Excel Trendline for Part of Data (2 Easy Methods)
0

Often it is essential to create an Excel trendline for part of data only while working in order to concentrate on certain trends, identifying outliers, ...

0
How to Calculate Safety Stock in Excel (6 Easy Methods)
0

Safety stock is essential in many sectors, specially in those that deal with inventory management and supply chain operations. It makes sure that a business ...

0
VBA to Call Sub From Another Workbook in Excel (With Examples)
0

It is often necessary to call a VBA subroutine (sub) from one workbook to another. This makes it simpler to organize and arrange your VBA projects because it ...

0
Excel Refresh All Not Working (Reasons and Solutions)
0

The "Refresh All" feature in Excel is essential for retrieving up-to-date data and sustaining correct analyses. However, at certain times this functionality ...

0
How to Use Excel SUMIF with Greater Than Criterion
0

In this modern world, life without Excel is kind of unimaginable. Almost in every working sector, Excel works as an essential tool. Numerous operations can be ...

0
Excel Slicer Vs Filter (Comparison & Differences)
0

When you get to deal with a huge spreadsheet in Excel, your work will be time consuming if you don’t have sufficient skills on filtering data. And to filter ...

0
Excel VBA Text to Columns for Fixed Width
0

Excel’s text to columns feature is one of the most popular features in Excel. Text to columns feature is applicable for delimited and fixed width type data. ...

0
How to Use Sales Run Rate Formula in Excel (3 Handy Examples)
0

Sales run rate is an important parameter in the field of business, finance, e-commerce, and retail that wants to predict future revenue based on current sales ...

Browsing All Comments By: Raiyan Zaman Adrey
  1. Reply Avatar photo
    Raiyan Zaman Adrey Jul 27, 2023 at 11:43 AM

    Dear COLYNN BURRELL,

    Thanks for reading our article. Try the following formula to resolve your issue.
    =IF(C5="Fail", TEXT("Replace, Review", "[$-409]@"), "")

    The formula indicates that if cell C5 contains text Fail, then the formula will return Replace, Review. If not, then the result it will return will be blank.

    Dataset

    Formula Breakdown
    TEXT(“Replace, Review”, “[$-409]@”)
    This formula returns Replace, Review each time.
    IF(C5=”Fail”, TEXT(“Replace, Review”, “[$-409]@”), “”)
    This formula returns Replace, Review only for the text string Fail.

    Now to add red color to the output, use the conditional formatting feature as illustrated in this article.
    ● Select the range D5:D9.
    ● Go through these steps: Home >> Conditional Formatting >> New Rule.

    conditional formatting

    ● Now, select Format only cells that contain from the Select a Rule Type section.
    ● Select Specific Text.
    ● Type Replace, Review.
    ● Click on Format.

    new formatting rule bar

    ● Select Font.
    ● Click on the arrow down icon in the color section.
    ● Select the color red.
    ● Click on OK.

    selecting color

    ● Click on OK again.

    formatting

    ● And the output will be as follows as you have desired.

    output

    Note: However, you can’t change the color of the output just after applying the formula. You have to use conditional formatting for that.

    If the problem still appears, kindly send us your Excel file and specify the exact problem you are facing.
    Thank you

    Best Wishes,
    Raiyan Zaman Adrey

  2. Reply Avatar photo
    Raiyan Zaman Adrey Jul 2, 2023 at 4:35 PM

    Dear MARK GROENING,

    Thank you for reading our articles. You wanted a formula to delete any random number and leave only text.
    You can follow method 3 and method 5 of this article for your problem. The formulas mentioned here should work with your problem.

    Using formula of method 3:
    =TEXTJOIN(“”, TRUE,IFERROR(MID(B5, SEQUENCE(LEN(B5)), 1) *1, “”))

    Image showing formula to remove text and leave numeric values

    Using formula of method 5:
    =SUM(MID(0&B5,LARGE(INDEX(ISNUMBER(–MID(B5,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1, 1)*10^ROW($1:$99)/10)

    Image showing formula to remove text and leave numbers only

    Hope, this worked for your problem.

    Regards,
    Raiyan Zaman Adrey

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo