Excel string function help

Cauld

So, I pull these numbers off through a telnet like connection. The numbers can be up to 12 characters long (including commas and the decimal point). The problem is that if the number is negative its surrounded by parentheses. This itself isn't a problem. The real problem is that if the number is big enough only the right parenthesis will show. I want to either strip off the right parenthesis if its the only one or add in the left one.

So all these numbers could be what I get (without the quotes):

"4895,044.37" 
" (90,663.10)"
"105,098.31)"  
" (1,315.90)"
"121,770.62)"
"     (0.39)"

There is an additional issue. The data I pull is a set number of characters, 12. So if the number is less than 12 digits it will have spaces in front of it to fill up that space. Also, the right parenthesis will always show up, if its a negative number. There's no comma for the millions division, I don't know why, its just the way it is.

so is there a good way to do this?

Daenris

Well... you can add a left parenthesis with the following:

=if(right(A1,1)=")",concatenate("(",A1),A1)

Jeice

To get rid of leading space use the TRIM function. Follow Daenris' code to fix the issue with the left parenthesis not showing up.

whuppins

Try this:

=IF(RIGHT(A6,1)=")",IF(ISERR(FIND("(",A6))=TRUE,"(",""),"")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,CHAR(160),""),CHAR(32),""),",","")

I know, it's gargantuan. But you're doing a lot of stuff here, and it's complicated by the fact that there are actually two different space characters depending on which character set you're working with. Here's what it's doing:

- Inserting a leading open paren if a close paren is found at the end of the string and no open paren is found

- Stripping all ANSI space characters

- Stripping all Unicode space characters

- Stripping all commas (seems like the easiest way to deal with the inconsistent comma format)

That seemed to work for me on all the examples you supplied. If it doesn't work, or if you need it to be more clever (like converting parens to minus signs), let me know via this thread or PM.

whuppins

Jeice wrote: »

To get rid of leading space use the TRIM function. Follow Daenris' code to fix the issue with the left parenthesis not showing up.

Yeah, I should mention: TRIM() only works for ANSI space characters. You can follow this advice if you're sure of the format your input will be, or you can use the SUBSTITUTE command like I did to ensure 100% compatability by explicitly removing both space character codes (32 and 160).

Edit: Also, while I love Daenris as a fellow brother in Excel, I should point out that his code will indiscriminately add an open paren whenever a close paren is found, so that the last example would actually be rendered "((0.39)". Mine will first check for the absence of an open paren before adding one.

Cauld

I should have noted I want to put this in a macro, not a cell... so how would I do it there. I'm not usually this bad at figuring it out, I swear! I'm extremely impressed with the answers though!

Daenris

whuppins wrote: »

Edit: Also, while I love Daenris as a fellow brother in Excel, I should point out that his code will indiscriminately add an open paren whenever a close paren is found, so that the last example would actually be rendered "((0.39)". Mine will first check for the absence of an open paren before adding one.

Shh... we're going to pretend I didn't even post that code snippet

whuppins

Daenris wrote: »

whuppins wrote: »

Edit: Also, while I love Daenris as a fellow brother in Excel, I should point out that his code will indiscriminately add an open paren whenever a close paren is found, so that the last example would actually be rendered "((0.39)". Mine will first check for the absence of an open paren before adding one.

Shh... we're going to pretend I didn't even post that code snippet

lol

Try this, OP:

[html]Dim val As String
val = Range("A1").Value
val = Application.WorksheetFunction.Substitute(val, ",", "") - strips commas
val = Application.WorksheetFunction.Substitute(val, Chr(32), "") - strips ANSI spaces
val = Application.WorksheetFunction.Substitute(val, Chr(160), "") - strips Unicode spaces
If Right(val, 1) = ")" Then - checks for a close paren at the last character
If Left(val, 1) <> "(" Then val = "(" & val - inserts an open paren if none is found
End If[/html]

It takes the value in cell A1 and applies the same logic as detailed in the formula above. There's probably a better way to do the character stripping via native VBA functions, but I never seem to have much luck when it comes to string functions in VBA. Always seems easiest just to steal the Excel functions.

And yeah, I know you won't be getting your data directly from a cell like in this example, but I'm assuming you already know how to throw your data stream into a variable via TextStream or whatever.

Edit: Now with comments!

Cauld

Awesome, thanks!