An Excel challenge

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...


Am i being really daft of will "Txet" not work at all????
 
Musicman said:
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.
Click to expand...

I'm with you on this one, it's a problem that just doesn't need solving

K.I.S.S
 
Lynton said:
Am i being really daft of will "Txet" not work at all????
Click to expand...

"Txet" is a User Defined Function as noted in the short VBA script above and not to be confused with the Excel Built in "Text" function.

Try it and you should see it will work.........
 
fabs said:
Right, tried both those codes:

Conor, Nothing happened when I ran it. Was I supposed to adjust something in the code for my specific spreadsheet?

lostsoulal, Excel didn't appear to like the Txet function. Kep wanting to replace it with Tver.
Click to expand...

Yeah, you're meant to replace 2 of the numbers. What column is the currency in and how many rows are there? I'll then write the code for you instead.
 
Musicman said:
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.
Click to expand...

This is a very good point actually. Perfect example is American and Australian dollars. How will you tell which is which by the symbol? Although, on saying that, I would say get the data imported into Access first because it becomes far easier to manipulate afterwards. Keep the excel sheet as a reference though in case you hit this problem
 
fabs said:
Cheers Conor. The column is AG and and there are 960 rows.
Click to expand...

Firstly, make sure AH is a blank column. Insert a column between if it isn't. This is your new code:

Sub temp()
Dim formattedCellValue As String

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

rowsInTotal = 960
columnWithCurrency = 33

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

End Sub
 
fabs said:
Right, tried both those codes:

Conor, Nothing happened when I ran it. Was I supposed to adjust something in the code for my specific spreadsheet?

lostsoulal, Excel didn't appear to like the Txet function. Kep wanting to replace it with Tver.
Click to expand...

Marc

Did you copy and past the script into the VBA module and the spreadsheet as it looks like you may have entered them manually?

I just tried it again this morning and it works - I am using Excel 2003



If you press ALT + F11 in Excel you should see the "Txet" function as follows if you have loaded the module correctly



regards

Roy
 
karmagarda said:
Firstly, make sure AH is a blank column. Insert a column between if it isn't. This is your new code:

Sub temp()
Dim formattedCellValue As String

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

rowsInTotal = 960
columnWithCurrency = 33

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

End Sub
Click to expand...

You sir are a genius!!! If you ever actually turn up to a London meet, I'm buying you a pint! :thumbs:

lostsoulal2 said:
Marc

Did you copy and past the script into the VBA module and the spreadsheet as it looks like you may have entered them manually?

I just tried it again this morning and it works - I am using Excel 2003



If you press ALT + F11 in Excel you should see the "Txet" function as follows if you have loaded the module correctly



regards

Roy
Click to expand...

I did use copy & paste but Excel just didn't like the Txet for some reason. I'm not convinced that our Office installation isn't full of bugs as there do seem o be a lot of little quirks that come up from time to time.

As you can see above, Conor's solution has done the trick but many thanks for you efforts as well. :thumbs:
 
You must log in or register to reply here.
Back
Top