Custom Number Format Text

I have a spreadsheet with custom formatting of a number to show the currency
name as text. This is a download from another application, so I have no
control of how it is formatted or the output. For example, I might see the
following cells in a given single column :

4,234 EUR
1,453 USD
2,999 CHF

These will all follow the custom format "#,##0 XXX" where XXX is the
currency type.

How can I split off the text portion of the format to another column? If my
orginal cell was A1, how could I make cell A2 = A1(CustomFormatTextOnly)

I am open to a VBA solution for this as well, but could not figure out how
to isolate the text.

Frank

Hi Frank,

Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
' Leaves the decimal separator in the string
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If ((Asc(b) > 47 And Asc(b) < 58) Or b = Application.DecimalSeparator)
Then StripTxt = StripTxt + b
Next i
End Function


--
Kind regards,

Niek Otten

"Frank & Pam Hayes" <[email protected]> wrote in message
news:XvWjf.1478$fY3.1110@trnddc01...
>I have a spreadsheet with custom formatting of a number to show the
>currency name as text. This is a download from another application, so I
>have no control of how it is formatted or the output. For example, I might
>see the following cells in a given single column :
>
> 4,234 EUR
> 1,453 USD
> 2,999 CHF
>
> These will all follow the custom format "#,##0 XXX" where XXX is the
> currency type.
>
> How can I split off the text portion of the format to another column? If
> my orginal cell was A1, how could I make cell A2 =
> A1(CustomFormatTextOnly)
>
> I am open to a VBA solution for this as well, but could not figure out how
> to isolate the text.
>
> Frank
>
>

=len(a4)-3 =left(a4,b4)

With some help I was able to build the following function to accomplish
this.

Option Explicit


Function CustomFormatText(Cell) As String

' This function was written by Frank Hayes of DuPont with help
' from Rich Eagles of Deloitte Consulting and
' Niek Otten from the microsoft.public.excel.misc newsgroup
'
' The function will strip out the text between the first set
' of quote marks in the target cell and write them to
' the cell from which the function was called.
'
' It is meant to be used when you are trying to strip out the
' currency name from a cell that is format as a number with
' a custom format such as #,##0 "EUR"
'

Dim i As Long
Dim x As String
Dim CustomFormatString As String

Dim FirstQuote As Boolean
Dim SecondQuote As Boolean

FirstQuote = False
SecondQuote = False

CustomFormatString = Cell.NumberFormat

For i = 1 To Len(CustomFormatString)
x = Mid$(CustomFormatString, i, 1)

' Find the first quote sign in the custom format
If FirstQuote = False Then
If Asc(x) = 34 Then
FirstQuote = True
GoTo GetNextCharacter
End If
End If

' Find the second quote sign in the custom format
If FirstQuote = True Then
If Asc(x) = 34 Then
SecondQuote = True
GoTo TheEnd
End If
End If

' Write out the characters between the first and second quote
If FirstQuote = True And SecondQuote = False Then
CustomFormatText = CustomFormatText + x
End If

GetNextCharacter:
Next i

TheEnd:
End Function