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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

C
  • Question
Extracting Number String from a Text String Cell
Replies
9
Views
803
Glenn Kennedy
G
M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
437
Alex Blakenburg
A
G
  • Question
Excel VBA to reply to existing email chain and add workbook as attachment
Replies
4
Views
213
gopalgriffith
G
B
  • Question
VBA to extract data from multiple folders and add to a master workbook plus identifying tags
Replies
2
Views
860
John_w
J
L
  • Solved
Fixing VBA code
Replies
3
Views
484
Joe4
Joe4

Forum statistics

Threads
1,215,019
Messages
6,122,707
Members
449,093
Latest member
Mnur

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