locked
Easy parse issue (I think) RRS feed

  • Question

  • I have a worksheet w/ data in column B that has information I need to split up.

    Current data   - # String() $##.##/String

    I need the first number (could be 1 or 2 digits) and the dollar amount.  Would like simple macro to replace current data with only the number and put dollar amount in column H.

    Tuesday, March 29, 2011 5:10 PM

Answers

  •  

    Try this

    Sub test()
    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    For RowCount = 1 To LastRow

       mystr = Range("B" & RowCount)
       myarray = Split(mystr, " ")
       FirstNum = myarray(0)
       SecondNum = myarray(2)
       SecondNum = Mid(SecondNum, 2)
       SecondNum = Val(SecondNum)
       Range("B" & RowCount) = FirstNum
       Range("H" & RowCount) = SecondNum
    Next RowCount

    End Sub


    jdweng
    Tuesday, March 29, 2011 9:28 PM

All replies

  •  

    Try this

    Sub test()
    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    For RowCount = 1 To LastRow

       mystr = Range("B" & RowCount)
       myarray = Split(mystr, " ")
       FirstNum = myarray(0)
       SecondNum = myarray(2)
       SecondNum = Mid(SecondNum, 2)
       SecondNum = Val(SecondNum)
       Range("B" & RowCount) = FirstNum
       Range("H" & RowCount) = SecondNum
    Next RowCount

    End Sub


    jdweng
    Tuesday, March 29, 2011 9:28 PM
  • Thanks, worked great.
    Friday, April 1, 2011 4:34 PM