Extract symbol (format) of currency from each cell
- Thread starter mgesh2002
- Start date
Debraj
Excel Ninja
Hi murugesh,
Can you please try the below function..
In VBA > Insert a New Module and paste the below Code
[pre]
[/pre]
In Excel sheet, if A2 contain a Numeric Figure, and formatted as Currency / Accounting, use UDF as =GetCurrency(A2)
Regards,
Deb
Can you please try the below function..
In VBA > Insert a New Module and paste the below Code
[pre]
Code:
Function GetCurrency(ByVal r As Range) As String
Application.Volatile
Static RegX As Object
If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Global = True
.Pattern = "[0-9-.,s]"
GetCurrency = .Replace(r.Text, "")
End With
End FunctionIn Excel sheet, if A2 contain a Numeric Figure, and formatted as Currency / Accounting, use UDF as =GetCurrency(A2)
Regards,
Deb
Marc L
Excel Ninja
Hi ! Another way in "VBA only" :
[pre]
[/pre]
[pre]
Code:
Function CurSymb(Rg As Range)
L = Left(LTrim(Rg.Text), 1)
R = Right(RTrim(Rg.Text), 1)
CurSymb = IIf(Val(L), IIf(Val(R), "", R), L)
End Function
Normally you don't need an UDF to extract the numbers from the currency !
You just have to enter a formula in a cell like "=A1" and apply a standard format ...
If you really want an UDF :
Function CurVal(Rg As Range)
CurVal = Rg.Value
End FunctionHi Murugesh,
Here is a formula based approach to get your numbers from curreny symbol.
Say, in A1, you have $101.
Now, in B1 write: =MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")) (CTRL+SHIFT+ENTER)to enter it as an array formula
in C1 write: SUMPRODUCT(- -ISNUMBER(MID(A1,ROW($A$1:$A$400),1)+0)) enter
in D1 write: MID(A1,B1,C1) enter
@Marc: Nice one...I like it...
Kaushik
Here is a formula based approach to get your numbers from curreny symbol.
Say, in A1, you have $101.
Now, in B1 write: =MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")) (CTRL+SHIFT+ENTER)to enter it as an array formula
in C1 write: SUMPRODUCT(- -ISNUMBER(MID(A1,ROW($A$1:$A$400),1)+0)) enter
in D1 write: MID(A1,B1,C1) enter
@Marc: Nice one...I like it...
Kaushik
shrivallabha
Excel Ninja
There are some more techniques discussed here:
http://www.ozgrid.com/forum/showthread.php?t=162314&p=595693#post595693
http://www.ozgrid.com/forum/showthread.php?t=162314&p=595693#post595693
Debraj
Excel Ninja
@ Shri..
Thanks for help to find it out.. Whenever I found some good techniques in any forum.. I copy the code in my OneNote and adapt the same when required.. My OneNote is full of lots of UDF like that, and my OneNote's paste link option was also disabled..
You may found a lots of techniques which are adapted by me and inspired by you / Marc and many other..
Happy to learn and share..
Regards,
Deb
Thanks for help to find it out.. Whenever I found some good techniques in any forum.. I copy the code in my OneNote and adapt the same when required.. My OneNote is full of lots of UDF like that, and my OneNote's paste link option was also disabled..
You may found a lots of techniques which are adapted by me and inspired by you / Marc and many other..
Happy to learn and share..
Regards,
Deb
shrivallabha
Excel Ninja
@Deb,
Great codes are mostly stolen. No offence. This is written at the foreword of:
VBA Developer's Handbook[2nd edition] by Ken Getz and Mike Gilbert.
So I personally don't see anything "unusual" in it.
Edit:= If this comes out as rude or offending post, I'd like to say sorry beforehand
Great codes are mostly stolen. No offence. This is written at the foreword of:
VBA Developer's Handbook[2nd edition] by Ken Getz and Mike Gilbert.
So I personally don't see anything "unusual" in it.
Edit:= If this comes out as rude or offending post, I'd like to say sorry beforehand
SirJB7
Excel Rōnin
@SirJB7
Hi, myself!
So long...
Stolen code? Who in the hell could...? Oh, I see, it was...
Code isn't stealable, either it's copyrighted and should be protected or shared upon certain conditions or simply copylefted. And of course that good and respectable people obey and respect this conditions, otherwise we must be wondering in what kind of world are we living.
Stealing code is comparable to taking coins from a blind's jar, it's like getting a candy from a baby, it isn't what I should call ethically -don't even mention morally- correct, it's... I have no words...
Regards!
PS: it's so useful sometimes that in their hurry perpetrators forget citing the source. And that's the major sin!
PS2: I swear with my right hand on the first edition of the book with the complete works of Socrates that I've never even think about doing such a disgusting thing... and I won't never do... (until next time I need to)
PS3: Who was the funny guy who added the in brackets addendum?
Hi, myself!
So long...
Stolen code? Who in the hell could...? Oh, I see, it was...
Code isn't stealable, either it's copyrighted and should be protected or shared upon certain conditions or simply copylefted. And of course that good and respectable people obey and respect this conditions, otherwise we must be wondering in what kind of world are we living.
Stealing code is comparable to taking coins from a blind's jar, it's like getting a candy from a baby, it isn't what I should call ethically -don't even mention morally- correct, it's... I have no words...
Regards!
PS: it's so useful sometimes that in their hurry perpetrators forget citing the source. And that's the major sin!
PS2: I swear with my right hand on the first edition of the book with the complete works of Socrates that I've never even think about doing such a disgusting thing... and I won't never do... (until next time I need to)
PS3: Who was the funny guy who added the in brackets addendum?
Friends, hope we can proceed further into the actual post.
@Marc: Great, thanks for the function (CurSymb), it works fine. But, CurVal does not give me the result. Please find the attachment.
https://www.dropbox.com/s/6lxbwq24nkl3c63/Marc%20and%20Kaushik.xlsx
@kaushik: Thanks for your thought... But, I could not use the formulas, as I tried in my workbook it does not give me the expected result. So, please check the attachment and advise me.
@Deb: You can add my name too in the inspired list. Great Job. Since, I am learning VBA, I am very keen to understand what is in the code and how it works, so if you could explain the functionality of the function (GetCurrency) would be much appreciable.
@Marc: Great, thanks for the function (CurSymb), it works fine. But, CurVal does not give me the result. Please find the attachment.
https://www.dropbox.com/s/6lxbwq24nkl3c63/Marc%20and%20Kaushik.xlsx
@kaushik: Thanks for your thought... But, I could not use the formulas, as I tried in my workbook it does not give me the expected result. So, please check the attachment and advise me.
@Deb: You can add my name too in the inspired list. Great Job. Since, I am learning VBA, I am very keen to understand what is in the code and how it works, so if you could explain the functionality of the function (GetCurrency) would be much appreciable.
NARAYANK991
Excel Ninja
Hi Murugesh ,
Marc's functions work correctly.
The problem is that all UDFs work on the basis of worksheet recalculation ; when you change the cell format , the UDFs are not triggered. Press CTRL ALT F9 and the functions will be recalculated and you will get the correct results.
Another problem is that when you format a cell as currency with the symbol , the symbol will appear in the display , but not in the formula bar ; the cell itself should just contain the numeric value. If your cell contains £111 , then it is no longer a number. Ensure that your cells contain only the numeric values , and the currency symbols appear because you have formatted the cells with the appropriate currency format.
Narayan
Marc's functions work correctly.
The problem is that all UDFs work on the basis of worksheet recalculation ; when you change the cell format , the UDFs are not triggered. Press CTRL ALT F9 and the functions will be recalculated and you will get the correct results.
Another problem is that when you format a cell as currency with the symbol , the symbol will appear in the display , but not in the formula bar ; the cell itself should just contain the numeric value. If your cell contains £111 , then it is no longer a number. Ensure that your cells contain only the numeric values , and the currency symbols appear because you have formatted the cells with the appropriate currency format.
Narayan
Marc L
Excel Ninja
One more point : for the recalculation, just add a line
in the beginning of the function like in the Deb's post ...
If you really need it Murugesh, I could optimize my CurVal function
for the case of a string cell instead of a numeric value currency formatted ...
Code:
Application.Volatilein the beginning of the function like in the Deb's post ...
If you really need it Murugesh, I could optimize my CurVal function
for the case of a string cell instead of a numeric value currency formatted ...
shrivallabha
Excel Ninja
@Marc L and Deb,
Suggestion: Both codes [Marc L's optimized solution] can be added to "the vault".
@Murugesh: The solution posted by Deb uses a powerful engine call "Regular Expressions". It is commonly used for picking up patterns which would be very difficult otherwise to detect programmatically. And it is used by multiple programs.
Deb's code uses it with what's called late binding (RegX is declared as generic object). If you want to refer it using early binding then goto Tools | References | Browse and Select VBScript.dll and it will give you references for Microsoft VBScript Regular Expressions 5.5.
A number defined in various formats which can be a currency number which is fed to the engine through pattern. See this discussion here:
http://stackoverflow.com/questions/354044/what-is-the-best-u-s-currency-regex
This is a site which has free tutorials for RegExp:
http://www.regular-expressions.info/tutorial.html
You should not take it up unless you want to be a full time programmer or want to pursue it out of personal interest and you are willing to spend time.
Suggestion: Both codes [Marc L's optimized solution] can be added to "the vault".
@Murugesh: The solution posted by Deb uses a powerful engine call "Regular Expressions". It is commonly used for picking up patterns which would be very difficult otherwise to detect programmatically. And it is used by multiple programs.
Deb's code uses it with what's called late binding (RegX is declared as generic object). If you want to refer it using early binding then goto Tools | References | Browse and Select VBScript.dll and it will give you references for Microsoft VBScript Regular Expressions 5.5.
A number defined in various formats which can be a currency number which is fed to the engine through pattern. See this discussion here:
http://stackoverflow.com/questions/354044/what-is-the-best-u-s-currency-regex
This is a site which has free tutorials for RegExp:
http://www.regular-expressions.info/tutorial.html
You should not take it up unless you want to be a full time programmer or want to pursue it out of personal interest and you are willing to spend time.
Marc L
Excel Ninja
Murugesh, these are the update functions :
[pre]
[/pre]
(Nothing new for the CurSymb function.)
These functions work with only one character currency's symbol.
For currencies typed like "EUR" or "USD" for example, another code is needed ...
Edit : a problem can occur with "mixed decimal separators", again a special code is needed.
[pre]
Code:
Function CurSymb(Rg As Range)
Application.Volatile
L = Left(LTrim(Rg.Text), 1)
R = Right(RTrim(Rg.Text), 1)
CurSymb = IIf(Val(L), IIf(Val(R), "", R), L)
End Function
Function CurVal(Rg As Range)
Application.Volatile
T = Trim(Rg.Text)
CurVal = CDbl(IIf(Val(T), T, Mid(T, 2)))
End Function(Nothing new for the CurSymb function.)
These functions work with only one character currency's symbol.
For currencies typed like "EUR" or "USD" for example, another code is needed ...
Edit : a problem can occur with "mixed decimal separators", again a special code is needed.
Debraj
Excel Ninja
Hi Faseeh,
* $120.05 and 120.05€ both are text with currency symbol.
* Either starting letter in CurrSymb (defined by L) or last letter is CurrSymb (Defined by R)
*
[/pre]
Something like.
=IF(ISNUMBER(VALUE(LEFT(A1))),IF(ISNUMBER(VALUE(RIGHT(A1,1))),"",RIGHT(A1,1)),LEFT(A1))[/code]
I hope now its clear..
@murugesh,
If you are interested, you may found this useful..
http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/
Regards,
Deb
* $120.05 and 120.05€ both are text with currency symbol.
* Either starting letter in CurrSymb (defined by L) or last letter is CurrSymb (Defined by R)
*
Code:
IIf(Val(L), IIf(Val(R), "", R), L)
If 1st letter is Numeric then pick Last Letter, and if Last letter is Numeric then pick 1st letter.
[pre][code]If L is a value = false then choose L
else if R is a Value = false then choose RSomething like.
=IF(ISNUMBER(VALUE(LEFT(A1))),IF(ISNUMBER(VALUE(RIGHT(A1,1))),"",RIGHT(A1,1)),LEFT(A1))[/code]
I hope now its clear..
@murugesh,
If you are interested, you may found this useful..
http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/
Regards,
Deb