Custom Number Format Text

[Frank & Pam Hayes]

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

 

[Niek Otten]

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

 

[Thiem]

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

 

[Frank & Pam Hayes]

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