Convert Text to Number within a column

  • Hi,


    I have a column in which I have the revenue of numerous companies, but they show up in shorthand form "$491.80M" to "$2.06B", how would I go about converting these to actual numbers "491,000,000" and "2,060,000,000"?


    In the past I have just filtered out the M or B for these values then multiplied the value by 1,000,000 or 1,000,000,000 respectively. But I would like to create a macro for this as it is very time consuming.


    i was thinking of a formula along the lines of:


    Dim Reng As Range
    Dim h As Range
    ColNum = Cells.Find(What:="Revenue").Column
    LastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
    Set Reng = Range(Cells(1, ColNum), Cells(LastRow, ColNum))
    For Each h In Reng
    If h.Value contains "M" Then
    h.Value = "mulitply by 1000000"
    End If
    Next h


    ^ I know the above is wrong, but any help would be greatly appreciated!

  • Re: Convert Text to Number within a column


    Lets assume you have the following in column A
    $491.80M
    $2.06B
    The following code will convert the text in Column A and put the real value in Column B. The assumptions are that each revenue entry starts with a "$" and ends with with "B" or "M"



    I hope this helps
    Anthony


    PS: You could do this without VBA using the following conditional statement within the worksheet

    Code
    =IF(RIGHT(RC[-1],1)="M",MID(RC[-1],2,(LEN(RC[-1])-2))*1000000,MID(RC[-1],2,(LEN(RC[-1])-2))*1000000000)


    If Column 1 contains e.g. $2.06M then this formula would convert it to 2060000. This assumes there only two conditions ->B or M


    Just another option

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!