Fetch currency name from data of different currencies

[Chartist]

I have a large sheet with transactions in many currencies including custom ones (some have symbols like $ or £, and some prefixed with letter codes like NGN).

Is there a way to extract the currency of each in a column next to it? Thanks.

 

[navic]

Suppose your data is in an 'A' column.
In the 'B' column, convert the values to text (using UDF by @xenou).

In the 'C' column place this formula below

=TRIM(SUBSTITUTE(B2,TEXT(A2,"#.##0,00"),""))

 

[Chartist]

Suppose your data is in an 'A' column.
In the 'B' column, convert the values to text (using UDF by @xenou).

In the 'C' column place this formula below

=TRIM(SUBSTITUTE(B2,TEXT(A2,"#.##0,00"),""))

Thanks for the reply. I couldn't make it work yet.

I have the cells with currency in column A now. I copied the TRIM(SUBSTITUTE(B2,TEXT(A2,"#.##0,00"),"") formula in column C.

I tried pasting values from column A to B and formatting it as text. What do I need to do in Column B? Thanks

 

[navic]

Here are the procedures, a little more detail

- Activate Sheet1
- Open VBE with ALT + F11
- Click Insert -> Module
- Copy this VBA code below to Standard Module in VBE for Excel

Function GetTextValue(ByVal arg As Range) As String
'by xenou
     GetTextValue = arg.Cells(1, 1).Text '//Cell value (or top left cell value if range is more than one cell)
End Function

- Copy this formula below to the 'B2' cell (copy down)

=GetTextValue(A2)

- In 'C2' cell copy this formula below (copy down)

=TRIM(SUBSTITUTE(B2,TEXT(A2,"#.##0,00"),""))

- Save the Workbook in *.xlsm or *.xls or *.xlsb format

 

[Chartist]

thanks friend, i followed those steps and tried it twice, but all columns are showing identical outputs
£500.00
NGN 630
etc
Not sure what I'm doing wrong.

 

[navic]

I don't know how else to help you.
I use Non-US regional setting.
See screenshots.

 

[Taul]

Hi
try changing the . (dot) to a , (comma) between the hash symbols #,##

=TRIM(SUBSTITUTE(B2,TEXT(A2,"#.##0,00"),""))

=TRIM(SUBSTITUTE(B2,TEXT(A2,"#,##0,00"),""))