Share via

Increase/decrease Decimal issue

Sara Phd 1 Reputation point
Apr 7, 2021, 9:41 PM

Hello there,

I copied regression output results from software to excel with the significance asterisk and didn't realize that results are with different decimal places. I used the decrease decimal option and it only worked for those without asterisk * and it is not increasing or decreasing the decimals in the numbers with asterisks*.

Is there any option to decrease the decimal as I need 3 decimal places with each value. or any way I can separate these * with the numbers and adjust the decimal places and then concatenate the two columns? I have attached one of my tables for your reference.

Please advise.

Regards,
Sara85410-sample-file.txt

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,752 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Lz._ 9,006 Reputation points
    Apr 8, 2021, 9:46 AM

    Hi @Sara Phd

    EDIT: When you have a minute please provide feedback to your previous thread (Delete multiple Columns from a worksheet with same header) - Thanks

    A manual way to do it. Once you've opened/imported your data in Excel:

    • Ctrl+H (Find & Replace)
    • Find what: ~*
    • Replace with: (nothing)
      => Replace All

    85767-demo.png

    then increase/decrease decimals as you want

    If you run at least Excel 2010 (for Windows) there's an easy way to automate this with Power Query/Get & Transform. If you're interested let me know and indicate how you get your data. You said "I copied regression output results from software to excel..." but you provided a text file so I'm a bit confused :)

    1 person found this answer helpful.
    0 comments No comments

  2. Emily Hua-MSFT 27,691 Reputation points
    Apr 8, 2021, 10:28 AM

    @Sara Phd

    Please refer to Lz-3068's reply, it is helpful.

    If you want to keep the asterisk, you may use the formula.

    I take the first column of your data as a sample, please refer to Lz-3068's reply to extract the digital part, in my sample I get the values (Column N).

    Then I enter the formula in Column O, =IF(N4="","",IF(LEN(N4)-FIND(".",N4)=3,N4&""&REPT("*",LEN(J4)-LEN(SUBSTITUTE(J4,"*",))),N4&REPT(0,3-FIND(".",N4))&REPT("*",LEN(J4)-LEN(SUBSTITUTE(J4,"*",))))).
    85708-capture15.png


    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.