Textbox format

[Alireza355]

Deal all,

I have a textbox that I want to apply some formatting to the text:

I know that if the textbox contains only numbers, by selecting "Standard" format, I can have the thousands seperator: 1,245,000

but the textbox that I have includes both text and number. for example:

Hello 1245000

I want to apply the thousand seperator to it:

Hello 1,245,000

How? :(

[ChipR]

Text parsing is a bit of a pain, but if you have to do it, try this.
Split() the text into separate words.
Check whether each word is a number with IsNumeric().
If the word is numeric, use FormatNumber() with the GroupDigits option.
Concatenate each of the words together again for the result.

[NeoPa]

Good idea.

The Join() function can also be used to put the various elements back together. It is effectively the opposite of Split(), even with the same optional separator parameter.

[ADezii]

Deal all,

I have a textbox that I want to apply some formatting to the text:

I know that if the textbox contains only numbers, by selecting "Standard" format, I can have the thousands seperator: 1,245,000

but the textbox that I have includes both text and number. for example:

Hello 1245000

I want to apply the thousand seperator to it:

Hello 1,245,000

How? :(

Is the Format always TEXT & SPACE & NUMBER?

[ADezii]

Deal all,

I have a textbox that I want to apply some formatting to the text:

I know that if the textbox contains only numbers, by selecting "Standard" format, I can have the thousands seperator: 1,245,000

but the textbox that I have includes both text and number. for example:

Hello 1245000

I want to apply the thousand seperator to it:

Hello 1,245,000

How? :(

To expand on ChipR's explanation in Post #2, and assuming components are delimited by a Space (" ") then:

Public Function fFormatString(strSomeString As String) As String
Dim varSplit As Variant
Dim strBuild As String
Dim intCounter As Integer

If Len(strSomeString) = 0 Then Exit Function

varSplit = Split(strSomeString, " ")

For intCounter = 0 To UBound(varSplit)
  If IsNumeric(varSplit(intCounter)) Then
    strBuild = strBuild & " " & FormatNumber(varSplit(intCounter), 0)
  Else
    strBuild = strBuild & " " & varSplit(intCounter)
  End If
Next

fFormatString = Trim$(strBuild)
End Function

? fFormatString("Help 12345 6792 me 656565656 Rhonda 989776 4434434 A B C 456") produces ==>

Help 12,345 6,792 me 656,565,656 Rhonda 989,776 4,434,434 A B C 456

[Alireza355]

More help needed

Dear Adezii,

Thank you so much for your kind help.

Please tell me how I can use the code you have given. (Where to put it and what to change, and how to make it run)

And I also thought this might be useful: the textbox I am trying to format is a calculated textbox.

=IIf([text51]=[text49],0,IIf([text51]>[text49],[text51]-[text49] & " (Credit)",[text49]-[text51] & " (Debit)"))

Thanx a lot.

[ADezii]

Dear Adezii,

Thank you so much for your kind help.

Please tell me how I can use the code you have given. (Where to put it and what to change, and how to make it run)

And I also thought this might be useful: the textbox I am trying to format is a calculated textbox.

=IIf([text51]=[text49],0,IIf([text51]>[text49],[text51]-[text49] & " (Credit)",[text49]-[text51] & " (Debit)"))

Thanx a lot.

1. Copy and Paste the entire Function Definition into the General Declarations Section of a Standard Code Module, the Function will then be contained within the Module and can be called from anywhere within the Application since it is Public.
2. Create an Unbound Text Box, and set its Control Source to:
   

   =fFormatString(IIf([text51] = [text49], 0, IIf([text51] > [text49], [text51] - [text49] & _
   " (Credit)", [text49] - [text51] & " (Debit)")))

[Alireza355]

Thanx a lot

WOW!!!!!!!!!!!! !!!!

Thanx a loooooooooooooo ooooooooooooooo ooooooooooooot

[ADezii]

WOW!!!!!!!!!!!! !!!!

Thanx a loooooooooooooo ooooooooooooooo ooooooooooooot

You are quite welcome, Alireza355.