Excel 2013 - Detect Cell Currency Format
I received an Excel file containing an extract of sales forecasts. There is no column explicitly defining currency unit. Currency unit is only shown by number formatting (GBP, EUR or CHF). Does anyone know how can I detect the currency format in order to apply appropriate exchange rate for the different currencies?
Example:
Displayed Number Format / Actual Number in Cell
200,000 GBP / 200000
10,000 CHF / 10000
500,000 EUR / 500000
Many thanks, Tym
microsoft-excel microsoft-excel-2013 cell-format
edited Jan 29 at 9:28
fixer1234
19k144982
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
add a comment |
I received an Excel file containing an extract of sales forecasts. There is no column explicitly defining currency unit. Currency unit is only shown by number formatting (GBP, EUR or CHF). Does anyone know how can I detect the currency format in order to apply appropriate exchange rate for the different currencies?
Example:
Displayed Number Format / Actual Number in Cell
200,000 GBP / 200000
10,000 CHF / 10000
500,000 EUR / 500000
Many thanks, Tym
microsoft-excel microsoft-excel-2013 cell-format
edited Jan 29 at 9:28
fixer1234
19k144982
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
add a comment |
I received an Excel file containing an extract of sales forecasts. There is no column explicitly defining currency unit. Currency unit is only shown by number formatting (GBP, EUR or CHF). Does anyone know how can I detect the currency format in order to apply appropriate exchange rate for the different currencies?
Example:
Displayed Number Format / Actual Number in Cell
200,000 GBP / 200000
10,000 CHF / 10000
500,000 EUR / 500000
Many thanks, Tym
microsoft-excel microsoft-excel-2013 cell-format
edited Jan 29 at 9:28
fixer1234
19k144982
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
I received an Excel file containing an extract of sales forecasts. There is no column explicitly defining currency unit. Currency unit is only shown by number formatting (GBP, EUR or CHF). Does anyone know how can I detect the currency format in order to apply appropriate exchange rate for the different currencies?
Example:
Displayed Number Format / Actual Number in Cell
200,000 GBP / 200000
10,000 CHF / 10000
500,000 EUR / 500000
Many thanks, Tym
microsoft-excel microsoft-excel-2013 cell-format
microsoft-excel microsoft-excel-2013 cell-format
edited Jan 29 at 9:28
fixer1234
19k144982
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
edited Jan 29 at 9:28
fixer1234
19k144982
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
edited Jan 29 at 9:28
fixer1234
19k144982
edited Jan 29 at 9:28
fixer1234
19k144982
edited Jan 29 at 9:28
fixer1234
19k144982
19k144982
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
asked Jan 28 at 17:33
Tym BurmanTym Burman
83
83
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Try the following User Defined Function:
Public Function txet(r As Range) As String
Dim s As String
s = Replace(r.NumberFormat, Chr(34), "")
txet = ""
If Len(s) < 3 Then Exit Function
s = Right(s, 3)
txet = s
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module - paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=txet(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
add a comment |
You can do this in VBA.
Sub CheckFormat()
Dim myCell, j, LastRow
Worksheets("Sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
Set myCell = Cells(j, "A")
Debug.Print myCell.NumberFormat
Next j
End Sub
I put your samples in A1, A2, and A3. The above code outputs this:
[$GBP] #,##0.00
[$CHF] #,##0.00
[$EUR] #,##0.00
You can then parse the first parts of that to decide what conversion rate to apply.
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399354%2fexcel-2013-detect-cell-currency-format%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try the following User Defined Function:
Public Function txet(r As Range) As String
Dim s As String
s = Replace(r.NumberFormat, Chr(34), "")
txet = ""
If Len(s) < 3 Then Exit Function
s = Right(s, 3)
txet = s
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module - paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=txet(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
add a comment |
Try the following User Defined Function:
Public Function txet(r As Range) As String
Dim s As String
s = Replace(r.NumberFormat, Chr(34), "")
txet = ""
If Len(s) < 3 Then Exit Function
s = Right(s, 3)
txet = s
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module - paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=txet(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
add a comment |
Try the following User Defined Function:
Public Function txet(r As Range) As String
Dim s As String
s = Replace(r.NumberFormat, Chr(34), "")
txet = ""
If Len(s) < 3 Then Exit Function
s = Right(s, 3)
txet = s
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module - paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=txet(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
Try the following User Defined Function:
Public Function txet(r As Range) As String
Dim s As String
s = Replace(r.NumberFormat, Chr(34), "")
txet = ""
If Len(s) < 3 Then Exit Function
s = Right(s, 3)
txet = s
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module - paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=txet(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
answered Jan 28 at 17:57
Gary's StudentGary's Student
13.9k31732
13.9k31732
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
add a comment |
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
HI Gary's Student, thanks so much for creating and explaining the UDF above. If you have the time I would really like to understand what is occurring on each line, no problem if you don't. This was really easy to implement and produced the output I was seeking. I realised last night that I didn't specify what output I was hoping to achieve, apologies for that. Thanks, Tym
– Tym Burman
Jan 29 at 8:49
add a comment |
You can do this in VBA.
Sub CheckFormat()
Dim myCell, j, LastRow
Worksheets("Sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
Set myCell = Cells(j, "A")
Debug.Print myCell.NumberFormat
Next j
End Sub
I put your samples in A1, A2, and A3. The above code outputs this:
[$GBP] #,##0.00
[$CHF] #,##0.00
[$EUR] #,##0.00
You can then parse the first parts of that to decide what conversion rate to apply.
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
add a comment |
You can do this in VBA.
Sub CheckFormat()
Dim myCell, j, LastRow
Worksheets("Sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
Set myCell = Cells(j, "A")
Debug.Print myCell.NumberFormat
Next j
End Sub
I put your samples in A1, A2, and A3. The above code outputs this:
[$GBP] #,##0.00
[$CHF] #,##0.00
[$EUR] #,##0.00
You can then parse the first parts of that to decide what conversion rate to apply.
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
add a comment |
You can do this in VBA.
Sub CheckFormat()
Dim myCell, j, LastRow
Worksheets("Sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
Set myCell = Cells(j, "A")
Debug.Print myCell.NumberFormat
Next j
End Sub
I put your samples in A1, A2, and A3. The above code outputs this:
[$GBP] #,##0.00
[$CHF] #,##0.00
[$EUR] #,##0.00
You can then parse the first parts of that to decide what conversion rate to apply.
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
You can do this in VBA.
Sub CheckFormat()
Dim myCell, j, LastRow
Worksheets("Sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
Set myCell = Cells(j, "A")
Debug.Print myCell.NumberFormat
Next j
End Sub
I put your samples in A1, A2, and A3. The above code outputs this:
[$GBP] #,##0.00
[$CHF] #,##0.00
[$EUR] #,##0.00
You can then parse the first parts of that to decide what conversion rate to apply.
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
answered Jan 28 at 17:59
Doug DedenDoug Deden
868213
868213
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
add a comment |
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
Hi Doug, I created a file CheckFormat_Test.xlsm with the currency-formatted numbers in column A on Sheet1 (starting at A1). I inserted a module into the file and pasted in the CheckFormat() code. Running the macro didn't seem to change the numbers to the format you listed above. Maybe this is significant, the input format actually has 2 decimal places (example 350,000.00 GBP). Many thanks for your support but I am too newbie to diagnose why this is not working, Tym
– Tym Burman
Jan 29 at 8:59
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
My sample code only sends output to the Immediate Window. It was just meant to demonstrate a way to detect the format of a cell, but you'll need to then decide what to do with that knowledge. The answer from Gary's Student does a better job of incorporating the result into the cells next to the formatted values.
– Doug Deden
Jan 29 at 15:16
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399354%2fexcel-2013-detect-cell-currency-format%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
