+ Reply to Thread
Results 1 to 16 of 16

how to extract a currency sign

  1. #1
    Registered User
    Join Date
    11-17-2015
    Location
    Luterbach
    MS-Off Ver
    MS office 2013
    Posts
    55

    how to extract a currency sign

    Hello
    I have a series of cells where I need to remove a currency sign which is on the front and end of the amount. The problem is that not all amounts having currencies sign. Can someone please help me to solve this issue?
    See for the example
    $55.21 USD
    24.31
    £99.16 GBP
    €233.56 EUR
    Last edited by Altamija; 02-04-2019 at 02:57 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: how to extract a currency sign

    Assuming those are in cells A1:A4, you can try this in B1:

    =IF(COUNTIF(A1,"* *"),LEFT(A1),"")

  3. #3
    Registered User
    Join Date
    11-17-2015
    Location
    Luterbach
    MS-Off Ver
    MS office 2013
    Posts
    55

    Re: how to extract a currency sign

    This formula extracting first character. i need to extract currency sing only.
    ot is it possible to remove all currencies sings from front and and the end - leave only the amounts ?

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: how to extract a currency sign

    But the first character is the currency sign, is it not?

    The formula in post #2 extracted (i.e. returned) all 3 currency signs from your sample in post #1. If there is no currency sign, the formula returns blank.

    If you would rather extract just the numbers, we can do that too.

  5. #5
    Registered User
    Join Date
    11-17-2015
    Location
    Luterbach
    MS-Off Ver
    MS office 2013
    Posts
    55

    Re: how to extract a currency sign

    It would be awesome if we can extract only numbers, (but needs to be currency format without currency sign).

    Thank you so much for your help.

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: how to extract a currency sign

    As I understand You want to 'convert' it to clear value, ignoring currency symbol and abbrev.
    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I've add VALUE just in case if You want to make some calculation on it later.

    not tested.
    Last edited by KOKOSEK; 02-04-2019 at 03:37 PM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: how to extract a currency sign

    Maybe this?
    Enter in B1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 $55.21 USD 55.21
    2 24.31* 24.31
    3 £99.16 GBP 99.16
    4 €233.56 EUR 233.56
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Registered User
    Join Date
    11-17-2015
    Location
    Luterbach
    MS-Off Ver
    MS office 2013
    Posts
    55

    Re: how to extract a currency sign

    It works perfect.
    would it be possible to adjust formula, so that will work with some negative signs which are included in some cases.

    See below:
    1,427.50 SEK
    -1,065.10 SEK
    2,218.60 CHF
    €652.09 EUR
    €1,715.77 EUR
    €616.24 EUR
    €509.96 EUR
    €48.50 EUR
    -€57.24 EUR

  9. #9
    Registered User
    Join Date
    11-17-2015
    Location
    Luterbach
    MS-Off Ver
    MS office 2013
    Posts
    55

    Re: how to extract a currency sign

    This works amazingly-
    It works perfect.
    would it be possible to adjust formula, so that will work with some negative signs which are included in some cases.

    See below:
    1,427.50 SEK
    -1,065.10 SEK
    2,218.60 CHF
    €652.09 EUR
    €1,715.77 EUR
    €616.24 EUR
    €509.96 EUR
    €48.50 EUR
    -€57.24 EUR

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: how to extract a currency sign

    How about:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by KOKOSEK; 02-04-2019 at 04:40 PM.

  11. #11
    Registered User
    Join Date
    11-17-2015
    Location
    Luterbach
    MS-Off Ver
    MS office 2013
    Posts
    55

    Re: how to extract a currency sign

    Amazing _ THANK YOU SO MUCH !!!

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to extract a currency sign

    Nice formula, KOKOSEK

    A slightly shorter version...
    =IFERROR(IF(ISNUMBER(A1),A1,--MID(A1,2,LEN(A1)-4)),MID(SUBSTITUTE(A1,",",""),1,LEN(SUBSTITUTE(A1,",",""))-4)*1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: how to extract a currency sign

    Hmm I never used -- (double minus) instead of VALUE. Nice to know.

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: how to extract a currency sign

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 1,427.50 SEK 1427.50
    2 -1,065.10 SEK -1065.10
    3 2,218.60 CHF 2218.60
    4 €652.09 EUR 652.09
    5 €1,715.77 EUR 1715.77
    6 €616.24 EUR 616.24
    7 €509.96 EUR 509.96
    8 €48.50 EUR 48.50
    9 -€57.24 EUR -57.24

  15. #15
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: how to extract a currency sign

    I've got errors, weird...

    Przechwytywanie.JPG
    Last edited by KOKOSEK; 02-04-2019 at 06:34 PM.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to extract a currency sign

    Maybe because the FIND return includes the space?
    Try =--left(A1,find(" ",a1)-1)

+ 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. Matching amounts with opposite sign - currency value
    By Hagen_F in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2017, 03:44 AM
  2. IF formula with Currency sign
    By dino4u86 in forum Excel General
    Replies: 6
    Last Post: 03-08-2016, 10:04 AM
  3. how to make the euro currency sign the default
    By joanmolamphy in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-19-2015, 11:07 AM
  4. Replies: 7
    Last Post: 06-17-2014, 02:15 PM
  5. [SOLVED] Sort By Currency Sign NOT Values
    By vinaynaran in forum Excel General
    Replies: 6
    Last Post: 09-11-2013, 12:18 PM
  6. Currency sign.
    By FjV in forum Excel General
    Replies: 2
    Last Post: 10-26-2005, 10:05 AM
  7. [SOLVED] Changing Dollar sign to another currency not listed in Excel
    By Rehan in forum Excel General
    Replies: 1
    Last Post: 04-12-2005, 12:06 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