snip last few characters

[bigdan]

i've tried dumping my bank transactions into excel. i want to remove the bank balance though. at the moment each transaction shows up in one cell. eg:

<table style="width: 525px; height: 52px;" border="0" cellpadding="0" cellspacing="0"><col width="64"><tr height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Jun 16, 2011 MERCHANDISE ESSO SUP $50.00 $4,379.07</td> </tr></table>

I don't want my bank balance in there so want to get rid of that part. This could be done I suppose by erasing the last 9 characters (assuming the comma and $ count as characters). But I have about 400 lines. How do I do this?

Also I have a transaction every other line, with a blank line in between. How do I get rid of the blanks?

Thanks!

 

[repairman615]

Hello,

This formula will grab everything to the left of the space before the second dollar sign.
(This formula will expect 2 dollar signs).

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324><COLGROUP><COL style="WIDTH: 243pt; mso-width-source: userset; mso-width-alt: 5924" width=324><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 243pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2758048 class=xl63 height=20 width=324>=LEFT(A1,FIND("$",A1,FIND("$",A1,1)+1)-2)</TD></TR></TBODY></TABLE>
Replace A1 to your cell

Would you like to use VBA to automatically delete the entire rows for the blanks?

If so are the rows even or odd and where to and from?

Jeff

 

[VoG]

Or maybe

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),"")

 

[tweedle]

The following should do the cleanup ...

[Make sure you have the sheet you want cleansed selected]

Sub CleanupBankData()
LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
For RowIdx = LastRow To 2 Step -1
    If Cells(RowIdx, 1) <> "" Then
        Cells(RowIdx, 1) = Left(Cells(RowIdx, 1), Len(Cells(RowIdx, 1)) - (InStr(1, StrReverse(Cells(RowIdx, 1)), "$", vbTextCompare)))
        
    Else
        Rows(RowIdx).Delete xlShiftUp
    End If
Next RowIdx
End Sub

FYI: Most bank exports provide either an xls or csv export option.

 

[VoG]

P.S. to delete blanks in column A

Sub DelBlanks()
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub