Need to convert text to number with 4 decimal places

[kilosub]

Hi All,

Receive data today which contain lots numbers in text format. How can I convert to number format with 4 decimal places.


Thanks in advance.

Cheers!!

Kilosub

 

[Jon von der Heyden]

Hi

Open the VBE, insert a new module and paste this into the code pane:

Public Sub MakeNums()
    With Selection
        .Value = Evaluate("if(row(),round(" & .Address & ",8))")
    End With
End Sub

Close the VBE again.

Select the range of text numbers.

Hit ALT+F8 and run the macro 'MakeNums'.

 

[kilosub]

Hi,

It works well but there's 2 issue as below:-

1. After conversion it leaves 2 decimal places, where can I extend it to 4 decimal place?

2. I tried highlighting several columns and run the macro. it return with error ""#value!". As the the data is in thousand and the conversion is mainly on the column, which runs from "G"cell until "CU" cell but not all of the column will need conversion.


Thanks and Cheers!!

Kilosub

 

[cgcamal]

Hi kilosub,

Another option with functions.

Assuming your numbers in text format are of the form "123456789" whithout decimal point, and are located in column A, begining in A1:

1-) copy in B1 this formula:

=VALUE(LEFT(A1,LEN(A1)-4)&"."&RIGHT(VALUE(A1),4))

2-) Change format of column B from "General" to "Number" with 4 decimal places (Select column B-->right click-->Cells format-->Number).

Hope this helps.

Regards

 

[kilosub]

Hi Cgcamal,

The number have decimal with trailing numbers which is inconsistant. I want to trancate and round up to have only 4 decimal placing.


Thanks and Cheers!!

Kilosub

 

[cgcamal]

May you show some sample input and desired output?

Regards

 

[kilosub]

Hi Cgcamal,

<TABLE style="WIDTH: 325pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=433><COLGROUP><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 114pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=152>Raw Data (Input)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=111>Desire Output</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 128pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=170>Contribute by Shandy Man</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>5454.55000000000020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>5454.5500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>5454.55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>-3780.67000000000010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>-3780.6700</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-3780.67</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>-36616.62000000000300</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>-36616.6200</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-36616.62</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>-3343.86999999999990</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>-3343.8700</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-3343.87</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>727.26999999999998</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>727.2700</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>727.27</TD></TR></TBODY></TABLE>

Shandy Man VB is great, if it can be done on selective column for the conversion that will be fantastic!!


Thanks and Cheers!!

Kilosub

 

[Peter_SSs]

Test this in a copy of your workbook.

Select the range with the data (if it the whole data area, or a specific set of columns etc then this manual selection could be avoided. More details would be needed) then run the macro.

<font face=Courier New><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> MakeNums2()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Selection<br>        <SPAN style="color:#00007F">For</SPAN> c = .Column <SPAN style="color:#00007F">To</SPAN> .Column + .Columns.Count - 1<br>            Columns(c).TextToColumns Destination:=Cells(1, c), DataType:=xlDelimited, _<br>                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _<br>                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _<br>                :=Array(1, 1), TrailingMinusNumbers:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        .NumberFormat = "0.0000"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Jon von der Heyden]

Mine updated:

Public Sub MakeNums()
    With Selection
        .Value = Evaluate("if(row(),round(" & .Address & ",4))")
        .NumberFormat = "0.0000"
    End With
End Sub

Please make sure only a contiguous range is selected.

 

[Peter_SSs]

Jon

Are you aware that that code fills blank cells with "0.0000" values and replaces any text values with #VALUE! errors?