An Excel challenge

Marc

Marc

TPer Emeritus
Messages
34,835
Edit My Images
Yes
Right, I have asked this question of one of my colleagues ad he has spent the best part of 2 hours trying (unsuccessfully) to find the answer. I was wondering how many peoples' day I could disrupt on here. :D

I have a group of cells which are formatted as Currency so that the €, £ or $ symbols appear. Is there any way that, in an adjacent cell, I can just display the appropriate currency symbol?

Your time begins....................:D
 
neil_g said:
use the "left" command?

e.g: =LEFT(A4, 3)

where A4 is the source cell and 3 is the number of characters in from the left, in your case it would be 1
Click to expand...

It was the first thing I tried Neil but, as the cell is formatted as currency rather than the symbol being typed in, it just returns the first number.
 
Trev Rich said:
You mean so it only appears when there is a value in the adjacent cell?
Click to expand...

Someone typed the values in as currency formatted but I need the currency symbol in a separate column. There are thousands of them so manually typing them in would be a nightmare and too much risk of human error.
 
Grrrr I need my bible ;)
Haven't got it here at work, and picking my son up straight after.. then taekwondo then dinner....
Might get time to look later, definitely tomorrow.
 
=IF(ISNUMBER(SEARCH("£",A4)),"£",IF(ISNUMBER(SEARCH("$",A4)),"$",IF(ISNUMBER(SEARCH("€",A4)),"€"," ")))

Replacing the A4 with the cell you want to check etc.. the last " " is what will show if no currency - hope that helps :D

:D
 
Trev Rich said:
Grrrr I need my bible ;)
Haven't got it here at work, and picking my son up straight after.. then taekwondo then dinner....
Might get time to look later, definitely tomorrow.
Click to expand...

No probs, cheers

neil_g said:
pfff.. i dont know then, im not sure you can reference something thats applied as part of the cell formatting.. i think trev may be your man for this one lol
Click to expand...

Thanks for trying anyway

Dark Star said:
But I'm playing - brb!
Click to expand...

I'm all excited. :D

PVO_Dave said:
=IF(ISNUMBER(SEARCH("£",A4)),"£",IF(ISNUMBER(SEARCH("$",A4)),"$",IF(ISNUMBER(SEARCH("€",A4)),"€"," ")))

Replacing the A4 with the cell you want to check etc.. the last " " is what will show if no currency - hope that helps :D

:D
Click to expand...

Sadly no for the same reason that the LEFT function won't work. Thanks anyway
 
copy and paste the cells with the numbers into word as a text, then import from word back into excel in a new column and paste as text. format column as text as well. That will give you the currency symbol as text now rather than the just a number. Following on from this crude but basic method, a macro with a function that goes through each cell one by one and deletes everything apart from the first character. Hopefully that should work.
 
hsuffyan said:
copy and paste the cells with the numbers into word as a text, then import from word back into excel in a new column and paste as text. format column as text as well. That will give you the currency symbol as text now rather than the just a number. Following on from this crude but basic method, a macro with a function that goes through each cell one by one and deletes everything apart from the first character. Hopefully that should work.
Click to expand...
Best suggestion yet but still doesn't work for the pound sign for some reason.

I doubt there is a proper solution to this problem. I suspect it will need a very complex bit of code if it can be done.
 
what version of excel you using??? i may have worked out an extremely crude way... It wont be text though... will still be a formatted cell... just with no price... will show as:

Currency Symbol
£35.00 £-
$25.00 $-
€ 45.00 € -


Is that what your after??

If your looking at doing the Count formula to count the number of £ or $ or (cant find the euro key!!!), then i'm not sure this method will work...

(Apologies for the horrible formatting on the table above... for some reason it wont let me lay it out nicely!!)
 
How about a function then?


Dim formattedCellValue As String

Dim rowToCheck As Integer
Dim rowsInTotal As Integer
Dim columnWithCurrency As Integer

rowsInTotal = 20
columnWithCurrency = 1

For rowToCheck = 1 To rowsInTotal
formattedCellValue = Sheet1.Cells(rowToCheck, columnWithCurrency).Text
Sheet1.Cells(rowToCheck, columnWithCurrency + 1) = Left(formattedCellValue, 1)
Next rowToCheck



Replace the following with:

columnWithCurrency = The number of the column with the currency value
rowsInTotal = The amount of rows you want to loop through


Warning! It overwrites the adjacent cell with the currency (see the columnWithCurrency + 1 bit)
 
hsuffyan said:
copy and paste the cells with the numbers into word as a text, then import from word back into excel in a new column and paste as text. format column as text as well. That will give you the currency symbol as text now rather than the just a number. Following on from this crude but basic method, a macro with a function that goes through each cell one by one and deletes everything apart from the first character. Hopefully that should work.
Click to expand...

-Rob- said:
Best suggestion yet but still doesn't work for the pound sign for some reason.

I doubt there is a proper solution to this problem. I suspect it will need a very complex bit of code if it can be done.
Click to expand...

Sadly, when I paste back into excel, it reverts to the currency format and when I change to text format, the currency symbol disappears.

tdcist said:
what version of excel you using??? i may have worked out an extremely crude way... It wont be text though... will still be a formatted cell... just with no price... will show as:

Currency Symbol
£35.00 £-
$25.00 $-
€ 45.00 € -


Is that what your after??

If your looking at doing the Count formula to count the number of £ or $ or (cant find the euro key!!!), then i'm not sure this method will work...
Click to expand...

I need the currency symbol in a separate column as the data is to be uploaded into an Access database where the currency symbol will link to an exchange rate table.
 
karmagarda said:
How about a function then?


Dim formattedCellValue As String

Dim rowToCheck As Integer
Dim rowsInTotal As Integer
Dim columnWithCurrency As Integer

rowsInTotal = 20
columnWithCurrency = 1

For rowToCheck = 1 To rowsInTotal
formattedCellValue = Sheet1.Cells(rowToCheck, columnWithCurrency).Text
Sheet1.Cells(rowToCheck, columnWithCurrency + 1) = Left(formattedCellValue, 1)
Next rowToCheck



Replace the following with:

columnWithCurrency = The number of the column with the currency value
rowsInTotal = The amount of rows you want to loop through


Warning! It overwrites the adjacent cell with the currency (see the columnWithCurrency + 1 bit)
Click to expand...

Unfortunately I haven't a clue what any of that means. You'd need to tell me how to actually utilise it. That said, it looks like it is looking for the symbol n the cell contents whereas te symbol is only in the formatting.
 
so access wont pick up the formatting then... Hmmm, ill keep trying
 
fabs said:
Unfortunately I haven't a clue what any of that means. You'd need to tell me how to actually utilise it. That said, it looks like it is looking for the symbol n the cell contents whereas te symbol is only in the formatting.
Click to expand...

Sorry! It's a macro. So find the macro button, and create a new macro (don't record, should be a button to create one). This should open up a Visual Basic screen.

Next, replace all the code so it looks like the following:



'STARTS HERE ---------------------------------------

Sub temp()
Dim formattedCellValue As String

Dim rowToCheck As Integer
Dim rowsInTotal As Integer
Dim columnWithCurrency As Integer

rowsInTotal = 20
columnWithCurrency = 1

For rowToCheck = 1 To rowsInTotal
formattedCellValue = Sheet1.Cells(rowToCheck, columnWithCurrency).Text
Sheet1.Cells(rowToCheck, columnWithCurrency + 1) = Left(formattedCellValue, 1)
Next rowToCheck

End Sub


'ENDS HERE------------------------------------------------




Click the green arrow at the top (it's a run button). Then switch back to your excel sheet and check if it worked!
 
Just a further note, you might have to click "View Macros" first, and then try to create the macro...
 
karmagarda said:
Sorry! It's a macro. So find the macro button, and create a new macro (don't record, should be a button to create one). This should open up a Visual Basic screen.

Next, replace all the code so it looks like the following:



'STARTS HERE ---------------------------------------

Sub temp()
Dim formattedCellValue As String

Dim rowToCheck As Integer
Dim rowsInTotal As Integer
Dim columnWithCurrency As Integer

rowsInTotal = 20
columnWithCurrency = 1

For rowToCheck = 1 To rowsInTotal
formattedCellValue = Sheet1.Cells(rowToCheck, columnWithCurrency).Text
Sheet1.Cells(rowToCheck, columnWithCurrency + 1) = Left(formattedCellValue, 1)
Next rowToCheck

End Sub


'ENDS HERE------------------------------------------------




Click the green arrow at the top (it's a run button). Then switch back to your excel sheet and check if it worked!
Click to expand...

Are you going to charge Fabs what you charge the west end hedge funds? :D
 
karmagarda said:
Sorry! It's a macro. So find the macro button, and create a new macro (don't record, should be a button to create one). This should open up a Visual Basic screen.

Next, replace all the code so it looks like the following:



'STARTS HERE ---------------------------------------

Sub temp()
Dim formattedCellValue As String

Dim rowToCheck As Integer
Dim rowsInTotal As Integer
Dim columnWithCurrency As Integer

rowsInTotal = 20
columnWithCurrency = 1

For rowToCheck = 1 To rowsInTotal
formattedCellValue = Sheet1.Cells(rowToCheck, columnWithCurrency).Text
Sheet1.Cells(rowToCheck, columnWithCurrency + 1) = Left(formattedCellValue, 1)
Next rowToCheck

End Sub


'ENDS HERE------------------------------------------------




Click the green arrow at the top (it's a run button). Then switch back to your excel sheet and check if it worked!
Click to expand...

karmagarda said:
Just a further note, you might have to click "View Macros" first, and then try to create the macro...
Click to expand...

Thanks Conor. Just about to head home but will give it a go in the morning.
 
And another quick note, the following line looks at the formatted text:

formattedCellValue = Sheet1.Cells(rowToCheck, columnWithCurrency).Text

Leaving out that .Text bit will give you the underlying value... I believe!
 
I do not lay claim to be the author, but this seems to work:

First enter this tiny User Defined Function (*):

Function txet(r As Range) As String
txet = ""
If r.Count = 1 Then
txet = r.Text
End If
End Function

This function returns a text string that matches the "as seen" value.

*
Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function as above -
Get out of VBA (Press Alt+Q)

then put the following formula in the cell adjacent to your currency field

=LEFT(Txet(A1),1)

change A1 to the location of your currency field

This will give you the desired Currency, although it will only work for currencies that are formatted on the left.

Hope that helps ...............
 
karmagarda said:
Good point. That'll be £7,000 please by tomorrow noon :naughty:
Click to expand...

I've passed it onto my boss. Will let you know when she puts the cheque n he post. ;)

lostsoulal2 said:
I do not lay claim to be the author, but this seems to work:

First enter this tiny User Defined Function (*):

Function txet(r As Range) As String
txet = ""
If r.Count = 1 Then
txet = r.Text
End If
End Function

This function returns a text string that matches the "as seen" value.

*
Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function as above -
Get out of VBA (Press Alt+Q)

then put the following formula in the cell adjacent to your currency field

=LEFT(Txet(A1),1)

change A1 to the location of your currency field

This will give you the desired Currency, although it will only work for currencies that are formatted on the left.

Hope that helps ...............
Click to expand...

Thanks, will try that one out tomorrow as well.
 
Just my 2p...

You'd be better off specifying the currency with a proper ISO identifier (GBP, EUR, USD, etc.)

Should you ever need to export the data to some other system, symbols may be ambiguous (e.g. $US or $CDN?).

I see that you're trying to fix an even worse situation, which is critical data (i.e. the currency) being stored as cell formatting, but the solution is a half way house.

If you're confident that the currencies in your sheet are unambiguous, it may be worthwhile adding an extra column calculating the ISO code to make it clear to anyone else coming after you.

I'm amazed that Excel doesn't allow you to do a conditional cell format the other way round - i.e. if cell A1=GBP, set cell format of cell B1 to currency [£] or whatnot.
 
what version of excel?
 
2003 :lol:

Last company I worked for had I think 2002 and that was way out of date.......

I'll have a play.. it must be able to be done........:shrug:

It's only getting the bloody data out of the cell....
 
You must log in or register to reply here.
Back
Top