Formula to look up left of "." ?

A

austi82110

Hi,

Anyones suggestions on this would be greatly appreciated.

I have a dataset that is in this format

997.46 USD
89.87 USD
3966.40 USD

I need to get an output from excel in another column that has

997
89
3966

I believe i should be using a combinations of the LEN and FIND
functions, but I have been unsuccessful. Any Ideas? Thanks:)
 
D

Dave Peterson

One way:

=INT(SUBSTITUTE(UPPER(A1)," USD",""))


Hi,

Anyones suggestions on this would be greatly appreciated.

I have a dataset that is in this format

997.46 USD
89.87 USD
3966.40 USD

I need to get an output from excel in another column that has

997
89
3966

I believe i should be using a combinations of the LEN and FIND
functions, but I have been unsuccessful. Any Ideas? Thanks:)
Click to expand...
 
V

Vito

austi82110 said:
Hi,

Anyones suggestions on this would be greatly appreciated.

I have a dataset that is in this format

997.46 USD
89.87 USD
3966.40 USD

I need to get an output from excel in another column that has

997
89
3966

I believe i should be using a combinations of the LEN and FIND
functions, but I have been unsuccessful. Any Ideas? Thanks:)
Click to expand...


You can try:

=INT(LEFT(A1,FIND(".",A1)-1))
 
C

Cutter

Assuming you will ALWAYS have the decimal point followed by 2 digits
followed by " USD" then:

=VALUE(LEFT(A1,LEN(A1)-7)) to have your final result numeric

=LEFT(A1,LEN(A1)-7) to have your final result text
 
A

austi82110

Thanks for your input!

Ill learn the variations of these formulas now, thanks!
 
Top