Need tiny bit of help with =Left + =find

[Vexorg]

Hey,

I have this in one cell:
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>
</caption> <thead> <tr> <th bgcolor="#c0c0c0">1013/1392</th> </tr> </thead> <tbody> </tbody> <tfoot></tfoot> </table>
and in another cell i want this to return:
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>
</caption> <thead> </thead> <tbody> <tr valign="TOP"> <td>1916 Requests (2297 Accounts)
</td> </tr> </tbody> <tfoot></tfoot> </table>


This is the macro im using:

=LEFT(C31,FIND("/",C31)-1)&" Requests ("&RIGHT(C31,FIND("/",C31))&" Accounts)"

Unfortunately this is returning '1916 Requests (/2297 Accounts)'. And I get an error every time i remove the "/"

Any help is appreciated.

Thanks

 

[Gerald Higgins]

Not tested but maybe

=LEFT(C31,FIND("/",C31)-1)&" Requests ("&RIGHT(C31,FIND("/",C31)+1)&" Accounts)"

By the way, that's not a macro, it's a formula, and it's not at all clear how 1013/1392 is meant to become 1916 . . . 2297. . .

 

[JoeMo]

try:

=LEFT(C31,FIND("/",C31)-1)&" Requests ("&RIGHT(C31,FIND("/",C31)-1)&" Accounts)"

 

[Gerald Higgins]

I should have tested it !

JoeMo's suggestion works on the sample given.
But be careful, I think it's a bit of a coincidence, based on the length of the numbers used.

For example, if your input data was 4/12, it doesn't work correctly.
Or 300/1000.

I think the MID function would work better than RIGHT.

Try this

=LEFT(C31,FIND("/",C31)-1)&" Requests 
("&MID(C31,FIND("/",C31)+1,255)&" Accounts)"

 

[jasonb75]

Or you could just go with

=SUBSTITUTE(C31,"/"," requests (")&" Accounts)"

 

[Gerald Higgins]

jasonb75 - that's definitely the neatest !

 

[Vexorg]

Haha sorry i meant formula, and i meant for this numbers to be the same. I posted this quick before i left work .

Thank you for the recommendations, i think i will go with jasonb's solution.