Extract currency code contained in cell number format ?

M

mystic2k

New Member
Joined
Nov 2, 2006
Messages
27
Hello all,

I'm working with downloads of financial information generated by SAP Business Warehouse. The values are in different currencies, and the currency is identified in the cell format. I'm trying to extract the 3-digit currency code for further processing.

Example :

Cell displays "-627.00 EUR"
Custom cell format = #,##0.00 "EUR"
I want to extract the "EUR" string into another cell.

Anyone knows how I can do that ? Thanks in advance for your help !
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
If its always the last 3 characters..

Code: 
=RIGHT(A1,3)

If its always after one space

Code: 
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

If its always after the last space (in case of multiple spaces)

Code: 
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))
 
Upvote 0
Thanks for your reply ! I had kind of forgotten about this thread before running into this issue again today :-/

Unfortunately this doesn't help. The currency code is embedded in the custom cell format itself and not in the cell value. Let's put it another way :

Say the cell A1 reads : "-627.00 EUR"
If I select the cell and change the format to "comma style" for example, it will now read : "627.00" and the "EUR" will have disappeared.
The actual value of the cell is "627" and the "EUR" is only part of the cell formatting... and this is what I am trying to extract into another cell to use it in further processing.

I guess only some voodoo-VBA code would achieve this (if even possible) so.... calling out to all VBA wizards out there !! :-) Thanks
 
Upvote 0
You may be able to use the .Text property rather than the .Value property. I believe .Text is the value as displayed...not sure. Alex

Edit: Sorry - thinking about VBA voodoo here. You'd need a custom function:

Rich (BB code): 
Function GetTextValue(ByVal arg As Range) As String
     GetTextValue = arg.Cells(1,1).Text  '//Cell value (or top left cell value if range is more than one cell)
End Function
 
Last edited:
Upvote 0
This works perfectly ! I can then extract the value I need with any text function.

Thanks a lot for the quick reply Alex !!
 
Upvote 0
Here is a non-VBA solution using an old Excel4 macro function. It is created as a function in a defined name and then used in, say, an adjacent cell. So, it has to be set up to work relative to the cell where you want the answer. Such as, say you have data in cell A1, make cell B1 active and then create a defined name (CTRL-F3 shortcut) say Maybe and in refers to enter =RIGHT(GET.CELL(53,A1),3)

After creating the defined name, go to a cell where there is the format and in the cell on its right enter the defined name (F3) in the cell, that is, =Maybe

This should show you the desired currency. HTH, Fazza
 
Upvote 0
Nice one Fazza - I wouldn't have thought this possible without VBA. Alex.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
126
6StringJazzer
6StringJazzer
C
  • Question Question
Extracting Number String from a Text String Cell
Replies
9
Views
1K
Glenn Kennedy
G
B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
361
etaf
etaf
D
  • Solved
How to control formatting of unprotected cells in a protected sheet? Ideas?
Replies
2
Views
356
Dman333
D
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
376
Jagadeshrao
J

Forum statistics

Threads
1,224,540
Messages
6,179,417
Members
452,912
Latest member
alicemil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top