sloppy excel skills... how clean can you be?

[Strata]

172/26

so i have that in a cell, and i need to split up the first part from the second part... i managed to figure it out, but i had such a sloppy way of doing it

=LEFT(B8,ROUNDDOWN(LEN(B8)/2,0))

I did that, to divide the cell in half and pull that data from it, (luckily none of the amounts would be 1302/3 or soemthing to that effect that woudl mess it all up

i then go:

=IF((RIGHT(B9,LEN(B9)-(LEN(B9))+1))="/",LEFT(B9,(LEN(B9)-1)),B9)

to make sure i clean up any moments there there is 172/, that formula manages to clean up the / and i can get myself the number


Now that's a downright sloppy long formula, anyone out there think of a sexier way to do this type of thing?

j.

 

[Scott Huish]

To get the left number:
=--(LEFT(A1,FIND("/",A1)-1))

To get the right number:
=--(REPLACE(A1,1,FIND("/",A1),""))

 

[Strata]

Wow.

that's a sexy piece of code.

thanks man!

 

[Strata]

how's this one:

left: =MID(B8,1,FIND("/",B8,1)-1)
right: =MID(B8,FIND("/",B8,1)+1,LEN(B8))

does -- make text become number?

 

[Scott Huish]

Yes, -- coerces a number represented as text into an actual number.

As far as your formula's go, if you are going to use MID starting at character 1, you should just use LEFT. I used REPLACE in the second formula (instead of RIGHT or MID) because it only requires 2 functions instead of 3.

 

[Strata]

thanks pepper.