Formula to look up left of "." ?
 

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:)


--
austi82110
------------------------------------------------------------------------
austi82110's Profile: http://www.excelforum.com/member.php...o&userid=30080
View this thread: http://www.excelforum.com/showthread...hreadid=497607

Formula to look up left of "." ?
 

Try

=TRUNC(SUBSTITUTE(A1,"USD",""))


--

Regards,

Peo Sjoblom


"austi82110" wrote
in message ...

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:)


--
austi82110
------------------------------------------------------------------------
austi82110's Profile:
http://www.excelforum.com/member.php...o&userid=30080
View this thread: http://www.excelforum.com/showthread...hreadid=497607

Formula to look up left of "." ?
 

One way:

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



austi82110 wrote:

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:)

--
austi82110
------------------------------------------------------------------------
austi82110's Profile: http://www.excelforum.com/member.php...o&userid=30080
View this thread: http://www.excelforum.com/showthread...hreadid=497607

--

Dave Peterson

Formula to look up left of "." ?
 

You can try:

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497607

Formula to look up left of "." ?
 

austi82110 Wrote:
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:)


You can try:

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497607

Formula to look up left of "." ?
 

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


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=497607

Formula to look up left of "." ?
 

Thanks for your input!

Ill learn the variations of these formulas now, thanks!


--
austi82110
------------------------------------------------------------------------
austi82110's Profile: http://www.excelforum.com/member.php...o&userid=30080
View this thread: http://www.excelforum.com/showthread...hreadid=497607