+ Reply to Thread
Results 1 to 10 of 10

Converting column of mixed currencies to GBP

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Bangor
    MS-Off Ver
    2010
    Posts
    20

    Converting column of mixed currencies to GBP

    I have a column of mixed currencies as below and want to create a new column with all these values converted to GBP.

    Can I set up a macro to pick from the spreadsheet the current exchange rate I have input for the 5 currencies used in the column and it creates a new column with all the amounts convered to GBP ?

    Have never done a Macro before so new to this.

    Total price
    £17.50
    £5.99
    AU $16.37
    EUR 87.00
    £10.99
    £7.99
    £35.50
    £10.99
    US $31.29
    EUR 45.47
    AU $33.91
    AU $214.11
    AU $214.11

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Converting column of mixed currencies to GBP

    You can do this very simply with a formula. However, it's hard to tell what numbers you are trying to convert from your post!!

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-10-2014
    Location
    Bangor
    MS-Off Ver
    2010
    Posts
    20

    Re: Converting column of mixed currencies to GBP

    See spreadsheet attached with actual data.

    Need formula to calculate the GBP value of each order and put this in Column C.

    Each time I run this Report will input in column G the current exchange rates for the 4 foreign currencies.

    Hope above is clear.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-10-2014
    Location
    Bangor
    MS-Off Ver
    2010
    Posts
    20

    Re: Converting column of mixed currencies to GBP

    Sending this again as not sure if attachment went in last reply.



    Trevor

    Sales reTotal price Price in GBP Conversion Rates on date of report
    6844 £17.50 AU $ to GBP 0.60
    6845 £5.99 US $ to GBP 0.80
    6846 AU $16.37 CA $ to GBP 0.61
    6847 EUR 87.00 EUR to GBP 0.85
    6848 £10.99
    6849 £7.99
    6850 £35.50
    6851 £10.99
    6852 US $31.29
    6853 EUR 45.47
    6854 AU $33.91
    6855 AU $214.11
    6856 CA $34.70
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Converting column of mixed currencies to GBP

    Is there any flexibility with the way you enter your data? Your current arrangement will require some messy formulae.

    Take a look and see if this is OK?
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Converting column of mixed currencies to GBP

    If you're stuck with that ghastly layout, you'll need something like this:

    =LOOKUP(10^10,RIGHT(B2,ROW(INDIRECT("1:"&LEN(B2))))+0)*VLOOKUP(TRIM(LEFT(B2,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},B2&"0123456789"))-1)),$F$2:$G$6,2,FALSE)

    and a slight modification to your Table
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Converting column of mixed currencies to GBP

    Hi Trevor,

    Hrer's another option.

    I have done the conversion as required by slightly changing the basic information and used a VLOOKUP formula - please see attached.

    As you enter the information the formula will automatically enter the "converted" £ value

    Will this work for you?

    Regards

    peterrc
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Converting column of mixed currencies to GBP

    Peterrc, is this materially different from Post 5?

  9. #9
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Converting column of mixed currencies to GBP

    Hi Glen,

    Not really.
    When I first looked at the post there was only your request for a sample file.
    For some reason I was having difficulty aswering the post with my attachment - i kept getting an error message.
    By the time I managed to post my reply you had beaten me to to it so I didn't know until then that you had also used a VLOOKUP formula.

    Regards

    peterrc

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Converting column of mixed currencies to GBP

    The layout in the original is truly horrible. Lets hope he/she can use one of the more sensible approaches....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to get the total of multiple currencies used in one column
    By Pubuduh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2014, 06:00 AM
  2. Sorting Column having different Currencies
    By MRKachhia in forum Excel General
    Replies: 2
    Last Post: 09-10-2013, 04:24 PM
  3. Replies: 4
    Last Post: 08-25-2012, 07:49 AM
  4. [SOLVED] How do you fill mixed row lables down a column?
    By Shagbark in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-24-2012, 10:24 AM
  5. converting currencies using exchange rate
    By Cyberslam in forum Excel General
    Replies: 8
    Last Post: 10-24-2010, 04:12 AM
  6. Converting USD to other currencies
    By mssimon in forum Excel General
    Replies: 2
    Last Post: 07-06-2009, 05:16 PM
  7. Converting all displaying values to other currencies
    By Nib in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-04-2006, 05:36 PM
  8. converting different currencies to one
    By alexander in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-11-2005, 04:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1