Need to convert the values

pandiyan · Oct 3, 2011

<table class="MsoNormalTable" style="width:110.0pt;margin-left:4.65pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="0" cellpadding="0" cellspacing="0" width="147"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; mso-border-top-alt:1.0pt;mso-border-left-alt:1.0pt;mso-border-bottom-alt: .5pt;mso-border-right-alt:.5pt;mso-border-color-alt:windowtext;mso-border-style-alt: solid;background:#D8D8D8;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> Column1
</td> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt;background: #D8D8D8;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> Column2
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">

14.2

</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">

14.2

</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 13MM
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 12.5MM+
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 2.5+MM
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:5;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 10.5M
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:6;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 15+M
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:7;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 9.2M+
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:8;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 12K
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:9;height:15.0pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73"> 21.5+K
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> <tr style="mso-yfti-irow:10;mso-yfti-lastrow:yes;height:15.75pt"> <td style="width:55.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext 1.0pt;mso-border-right-alt:solid windowtext .5pt;padding: 0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="73"> 2.1K+
</td> <td style="width:55.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="73">
</td> </tr> </tbody></table>
Hi,

I need VBA script to convert these values. I need results in column2. If the values are in numeric print as it is. If MM or +MM or MM+ comes I need result like = value*1000000.
If M or +M or M+ comes I need result like =value*1000 and if its K or +K or K+ result should be = value*1000.

Can any one help me?

Thanks,

RomulusMilea · Oct 3, 2011

Hello,

For this particular case, you do not need a VBA code, please use below formula, I assumed your input data is in the cell B43:

=IF(B43="","No input data!",IF(COUNTIF(B43,"*MM*")>0,10^6*SUBSTITUTE(SUBSTITUTE(B43,"+",""),"MM",""),IF(LEN(B43)-LEN(SUBSTITUTE(B43,"M",""))=1,10^3*SUBSTITUTE(SUBSTITUTE(B43,"+",""),"M",""),IF(LEN(B43)-LEN(SUBSTITUTE(B43,"K",""))=1,10^3*SUBSTITUTE(SUBSTITUTE(B43,"+",""),"K",""),B43))))

Please confirm this solves your request, as expected. Thank you.

Romulus.

Rick Rothstein · Oct 3, 2011

Give this macro a try (change the value being assigned in the Const statement as needed)...

Code:

Sub ConvertKandMandMM()
  Dim X As Long, LastRow As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Columns("B")
    .Value = Columns("A").Value
    .Replace "+", "", xlPart
    .Replace "K", "*1000", xlPart
    .Replace "M", "*1000", xlPart
    .Replace " ", "", xlPart
  End With
  For X = StartRow To LastRow
    Cells(X, "B").Value = Evaluate("=" & Cells(X, "B").Value)
  Next
End Sub

Rick Rothstein · Oct 3, 2011

Rick Rothstein said:
Give this macro a try...

Or if you are willing to use a formula solution, then try this...

=LOOKUP(9999,--LEFT(A1,ROW(INDIRECT("1:99"))))*IF(ISNUMBER(SEARCH("MM",A1)),1000000,IF(ISNUMBER(SEARCH("M",A1)),1000,IF(ISNUMBER(SEARCH("K",A1)),1000,1)))

The above formula assumes the number in your cells will never be greater than 9999; if they could be, then just change the 9999 to number larger than the largest possible number.

pgc01 · Oct 3, 2011

Hi

Similar to Rick's solution:

=-LOOKUP(0,-LEFT(A1,ROW(INDIRECT("1:99"))))*LOOKUP(0,-1/SEARCH({"","K","M","MM"},A1),{1,1000,1000,1000000})

Rick Rothstein · Oct 3, 2011

pgc01 said:
Similar to Rick's solution:

=-LOOKUP(0,-LEFT(A1,ROW(INDIRECT("1:99"))))*LOOKUP(0,-1/SEARCH({"","K","M","MM"},A1),{1,1000,1000,1000000})

Similar, yes, but much more elegant. Nicely done!

pgc01 · Oct 3, 2011

Thanks Rick. It almosty compensates a really dumb moment here in post #5

http://www.mrexcel.com/forum/showthread.php?p=2883847#5

Luckily Barry was hovering and pointed it out.

Need to convert the values

pandiyan

New Member

Excel Facts

RomulusMilea

Board Regular

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

pgc01

MrExcel MVP

Rick Rothstein

MrExcel MVP

pgc01

MrExcel MVP

Similar threads

Forum statistics

Share this page

Need to convert the values

pandiyan

New Member

Excel Facts

RomulusMilea

Board Regular

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

pgc01

MrExcel MVP

Rick Rothstein

MrExcel MVP

pgc01

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock