To "credit" or not?

[Amanda1]

That's me.

I thought I might be getting to the "toddling" stage in this VB, but no - back to crawling.

I have a column of data that has been screen dumped from a mainframe. It is mainly all numbers, with some cells being blank or having hyphens etc in them.

The numbers have been written as 2 345 678.00DB to indicate debit entries, and to indicate credit entries it is just the number, no minuses or anything like that.

It is not a problem to get it all into number format etc etc, but what I can't work out is how do I turn the credits (numbers without any trailing letters) into a number having a "-" at the front of the number that M/Soft will accept as a legitimate negative number? Doing text to columns etc doesn't work, it needs to be script driven.


Pls point me in the right direction?

Thanks & take care

Amanda

[johnske]

Hi Amanda,

You mean something like this?[vba]Sub TryThis1()
If IsNumeric(Range("a2")) Then Range("a2") = -Range("a2")
End Sub[/vba]

[Wolfgang]

hi amanda...

my -.5 euro cents...

best,
wolfgang

[mvidas]

Hi Amanda,

Try the following:[vba]Sub AmandaToCreditOrNot()
Dim RegEx As Object, RegC As Object, CLL As Range, TempArr() As Variant, i As Long
Dim tStr As String
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.IgnoreCase = True
RegEx.Pattern = "\b([\d_]+?)(DB)?\b"
For Each CLL In Range("A1", Range("A65536").End(xlUp))
tStr = Replace(CLL.Text, ".", "_")
If RegEx.Test(tStr) Then
Set RegC = RegEx.Execute(tStr)
ReDim TempArr(RegC.Count)
For i = 0 To RegC.Count - 1
TempArr(i) = CDbl(IIf(Len(RegC.Item(i).SubMatches(1)) > 0, "", "-") & _
Replace(RegC.Item(i).SubMatches(0), "_", "."))
Next
CLL.Resize(1, RegC.Count).Value = TempArr
End If
Next
Set RegEx = Nothing
Set RegC = Nothing
Set CLL = Nothing
End Sub[/vba]Right now it does the used cells in column A starting with A1, but feel free to adjust the Range("A1", Range("A65536").End(xlUp)) to whatever suits you best.

Matt

[Amanda1]

Hi Guys,

Getting there but not quite. Currently there are no "-" in the column, I need to get them in where there is a number that doesn't have any, & I need them to be in the front of the number.

Then when that is done, I can remove the "DB" portion from the rest - hence I will then have debits and credits in a format M/Soft can understand and work with. Here is a small extract from the column which will probably illustrate better.

 912 303.78DB 
1 436 622.72DB
4 438.24
97 136.60
97 136.00DB
161 188.82DB 

In this small extract/example it would be the 3rd & fourth numbers that I would need to have a minus in front of, (then I can delete the "DB"). Everything is in Col B, total number of rows can vary as it is updated on a constant basis & obviously it would have to go through all the cells in the column because they are in random order.

Cheers

Amanda

[mvidas]

Hi Amanda, If your initial data is this (in column B)

 912 303.78DB 
1 436 622.72DB
4 438.24
97 136.60
97 136.00DB
161 188.82DB 

Are you looking to have the following (with the space being the separator between columns B and C)

 912 303.78
1 436 622.72
4 -438.24
97 -136.60
97 136.00
161 188.82 

Are you only looking to fix the db/cr for the last number in the cell, or for each of them?

[Amanda1]

Hi,

Sorry Matt, must be something about a time lapse in the southern hemisphere - when I posted mine back, yours wasn't on there - now it suddenly is & in front of mine. Not quite sure how that works - but not a problem.

Your example above is what I'm looking for, other than if you take the 3rd line, you have "4 -438.24" & the same with the next one of "97 -136.60" However, the "-" is in the wrong place. It must be in front of the first number in the cell.

I.E the format 4 438.24 is actually four thousand, four hundred and thirty eight - that is just the way it comes down on the screen dump - unfortunately; so the "minus" needs to be in front of the first "4", not the second one.

I reckon I can manage to get the "DB" portion out, so that I am not worried about, but I can't get a "-" in front of the complete number, and only on those that do not end with "DB". (By the way the "DB" has no spaces in front of it, it literally is at the end of the number).

Does that make a bit more sense?

Cheers & take care

Amanda

[Shazam]

Here is a formula solution.


="-"&SUBSTITUTE(LEFT(A1,LEN(A1)-2)," ","",1)

Or

=IF((RIGHT(A3,2)="DB"),"","-"&A3)

[mvidas]

Where's the "SHAZAM!"?

Amanda,
So after running, you want the above numbers to look like:

912303.78
1436622.72
-4438.24
-97136.6
97136
161188.82 

? I didn't realize that the whole string was just one number. If that is correct, the following should work fine for you:[vba]Sub AmandaToCreditOrNot()
Dim RegEx As Object, RegC As Object, CLL As Range, tStr As String
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = False
RegEx.IgnoreCase = True
RegEx.Pattern = "^([\d\. ]+?)(DB)?$"
For Each CLL In Range("B1", Range("B65536").End(xlUp))
tStr = Trim$(CLL.Text)
If RegEx.Test(tStr) Then
Set RegC = RegEx.Execute(tStr)
CLL.Value = CDbl(IIf(Len(RegC.Item(0).SubMatches(1)) > 0, "", "-") & _
Replace(RegC.Item(0).SubMatches(0), " ", ""))
End If
Next
Set RegEx = Nothing
Set RegC = Nothing
Set CLL = Nothing
End Sub[/vba]Matt

[Shazam]

After seeing mvidas code here is a formula solution:

=IF((RIGHT(A1,2)="DB"),SUBSTITUTE(LEFT(A1,LEN(A1)-2)," ","",1),"-"&SUBSTITUTE(A1," ",""))

But I like mividas code better.

[mvidas]

Personally I think a formula way might be the better way to go (if vba isn't necessary....), though I did have a problem with yours when the cell had a space at the end (result of copy/paste from website). A formula way to get around that, and also to treat the number as a value, would be:
=VALUE(IF(RIGHT(TRIM(A1),2)="db",LEFT(SUBSTITUTE(A1," ",""),LEN(SUBSTITUTE(A1," ",""))-2),"-"&SUBSTITUTE(A1," ","")))

But I know Amanda is seemingly loving vba, so it might be better for her. Although, the routine I suggested is more of the intermediate level, as it does use regular expressions.
Amanda- I could probably create a routine that does the same thing, but without regular expressions, just so you might be able to follow it along to see what it is doing. Although, you should learn regexp as well, as it is immensely useful and very quick.


And I still say you should start out every post with "SHAZAM!" I'd do it, but "MVIDAS!" doesn't have quite the same ring.

[Shazam]

Hi mvidas,

The formula that I provided works but you have a point that when copy and paste from the website it will bring junk data along with it. So here is a slight modification to my formula.

SHAZAM!


=IF((RIGHT(TRIM(A1),2)="DB"),SUBSTITUTE(LEFT(A1,LEN(TRIM(A1))-2)," ","",1),"-"&SUBSTITUTE(A1," ",""))

[mdmackillop]

If you don't want to delve into the mysteries of RegExp yet, you could try either the sub or function options:
[VBA]
Sub Numbers()
Dim cel As Range
For Each cel In Selection
cel.Value = Number(cel)
Next
End Sub

Function Number(data) As Double
If InStr(1, data, "DB") > 1 Then
Number = -Replace(Replace(data, "DB", ""), " ", "")
Else
Number = --Replace(data, " ", "")
End If
End Function

[/VBA]