How to round number with asterisks?

[Wiley]

Good morning,

I am curious whether it is possible to round a number with asterisks, but keeping those characters in the output? I have a regression tables with many decimals and significance levels ***, ** or * moreover some numbers are in parentheses. Would it be possible/exist a simple way to round the numbers to 2 or 3 decimals? I do not put my code - nothing works for me.

<colgroup><col style="mso-width-source:userset;mso-width-alt:8777;width:180pt" width="240"> <col style="width:48pt" width="64" span="3"> </colgroup><tbody>
[TD="class: xl67, width: 240"]var1
[/TD]
[TD="class: xl70, width: 64"]0.969***
[/TD]
[TD="class: xl70, width: 64"]0.935***
[/TD]
[TD="class: xl70, width: 64"]0.934***[/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl70"](0.0112)[/TD]
[TD="class: xl70"](0.00947)[/TD]
[TD="class: xl70"](0.00997)[/TD]

[TD="class: xl68"]var2
[/TD]
[TD="class: xl69"]-0.0171**
[/TD]
[TD="class: xl69"]-0.0138***[/TD]
[TD="class: xl69"]-0.0138*
[/TD]

[TD="class: xl68"]
[/TD]
[TD="class: xl69"](0.00342)[/TD]
[TD="class: xl69"](0.00297)[/TD]
[TD="class: xl69"](0.00297)[/TD]

[TD="class: xl67"]dummy
[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]0.0614***[/TD]
[TD="class: xl70"]0.0615*
[/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"](0.0102)[/TD]
[TD="class: xl70"](0.0102)[/TD]

</tbody>

Kind regards,
Wiley

 

[Tim_Excel_]

Function NumOnly(txt As String) As Long
Dim x
x = Split(Trim(txt), Chr(32))
NumOnly = Val(x(UBound(x)))
End Function
Sub roundnum()

For Each cell in ThisWorkbook.Sheets(1).Range("B1:C6")
cell.Value = Round(NumOnly(cell.Value), 2) & Right(cell.Value, LEN(cell.Value)-LEN(NumOnly(cell.Value))
Next cell
End Sub

Let me know if this works for you. Adjust the ranges to your preferences

 

[Wiley]

This code in my case stops at line "cell.Value =..." - in red
Thank you for quick reply and code
wiley

 

[Tim_Excel_]

[COLOR=#333333]Function NumOnly(txt As String) As Long[/COLOR]Dim x
x = Split(Trim(txt), Chr(32))
NumOnly = Val(x(UBound(x)))
End Function

Sub roundnum()

For Each cell in ThisWorkbook.Sheets(1).Range("B1:C6")
cell.Value = Round(NumOnly(cell.Value), 2) & Right(cell.Value, LEN(cell.Value)-LEN(NumOnly(cell.Value)))
Next cell 
[COLOR=#333333]End Sub[/COLOR]

forgot a parenthesis

 

[Wiley]

It shows "Compile error - variable required can't assign" in the same line highlited NumOnly:
cell.Value = Round(NumOnly(cell.Value), 2) & Right(cell.Value, Len(cell.Value) - Len(NumOnly(cell.Value)))

Thanks in any case