Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format Text
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" 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format Text
=len(a4)-3 =left(a4,b4) -- Thiem ------------------------------------------------------------------------ Thiem's Profile: http://www.excelforum.com/member.php...o&userid=27474 View this thread: http://www.excelforum.com/showthread...hreadid=490117 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format Text
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change number format from text to number? | New Users to Excel | |||
Converting number or text to a Date Format | New Users to Excel | |||
Format Number to Text | Excel Worksheet Functions | |||
Custom Number Format for Weight | Excel Discussion (Misc queries) | |||
Custom format a text cell with square brackets: [ ] ? | Excel Discussion (Misc queries) |