Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default I want to extract the pence from a currency sum

Hello

I am working on a sheet where I need to breakdown a currency value into pounds and pence in separate columns without the pence showing the decimal point.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default I want to extract the pence from a currency sum

Hi Julie,

Am Wed, 1 Feb 2017 08:24:05 -0800 (PST) schrieb JulieS:

I am working on a sheet where I need to breakdown a currency value into pounds and pence in separate columns without the pence showing the decimal point.

i.e. 123.58 in B1 becomes 123 in C1 and 58 in D1


try in C1:
=INT(B1)
and in D1:
=MOD(B1,1)


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default I want to extract the pence from a currency sum

Hi Claus

Thank you for that, is there anyway the decimals can be populated without the decimal point so it literally becomes 58?

Thank you!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default I want to extract the pence from a currency sum

Hi Julie,

Am Wed, 1 Feb 2017 08:50:38 -0800 (PST) schrieb JulieS:

Thank you for that, is there anyway the decimals can be populated without the decimal point so it literally becomes 58?


multiply by 100:
=MOD(B1,1)*100


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default I want to extract the pence from a currency sum

On Wednesday, February 1, 2017 at 4:58:23 PM UTC, Claus Busch wrote:
Hi Julie,

Am Wed, 1 Feb 2017 08:50:38 -0800 (PST) schrieb JulieS:

Thank you for that, is there anyway the decimals can be populated without the decimal point so it literally becomes 58?


multiply by 100:
=MOD(B1,1)*100


Regards
Claus B.
--
Windows10
Office 2016


Thanks Claus, that works when I have a number but when the decimal is 00, it just populates as 0, and I don't know how to populate the second zero, any ideas?

I really appreciate your help on this!


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default I want to extract the pence from a currency sum

Hi,

Am Wed, 1 Feb 2017 09:07:24 -0800 (PST) schrieb JulieS:

Thanks Claus, that works when I have a number but when the decimal is 00, it just populates as 0, and I don't know how to populate the second zero, any ideas?


format D1 with custom numberformat 00


Regards
Claus B.
--
Windows10
Office 2016
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default I want to extract the pence from a currency sum

On Wednesday, February 1, 2017 at 5:19:54 PM UTC, Claus Busch wrote:
Hi,

Am Wed, 1 Feb 2017 09:07:24 -0800 (PST) schrieb JulieS:

Thanks Claus, that works when I have a number but when the decimal is 00, it just populates as 0, and I don't know how to populate the second zero, any ideas?


format D1 with custom numberformat 00


Regards
Claus B.
--
Windows10
Office 2016


Claus - Thank you so much! It works, you star! I really appreciate this.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pence per mile Double_R New Users to Excel 1 February 8th 09 08:31 PM
how can i convert £28.34 to 2834 pence Rina Excel Worksheet Functions 3 August 4th 06 10:29 PM
WHY IS MY CURRENCY SUM ONE PENCE OUT? Tania Excel Worksheet Functions 1 March 9th 06 04:23 PM
How do I add Pounds, Shillings and Pence? alanturner12 Excel Discussion (Misc queries) 1 July 24th 05 02:45 PM
Pence field to currency? fbm Excel Worksheet Functions 1 April 28th 05 11:04 AM


All times are GMT +1. The time now is 06:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"