Solved: Text Format

[tqm1]

Reference:http://www.excelforum.com/showthread.php?p=1790394&posted=1#post1790394

Dear Experts


Private Sub CommandButton2_Click()
Dim a As Long, b As Long, c As Long

a = 300
'300 this is weight in kgs

b = Int(a / 40)
'7 this is integer weight of a

c = a Mod 40
'20 this is mod weight of a

Range("a10").Value = Format(b, "###") + "-" + Format(c, "###")
End Sub


Range("a10") displays 20-Jul instead of text "7-20"

What format should I apply for range("a10")?

If I apply General or Text format then it shows dateserial=39283
What is worng?

Please help me to display correct text="7-20" in range("a10")

[bigdoggit]

You need to change your #'s to &'s. Excel interperates your numbers as a date. The @ is for text, so excel will treat it as text and not try to turn it into a date. Will this work for your situation?

Range("e10").Value = Format(b, "&&&") + "-" + Format(c, "&&&")



Here are the characters you can use to create a format expression for strings per the excel help file on such.

@ Character placeholder. Display a character or a space. If the string has a character in the position where the at symbol (@) appears in the format string, display it; otherwise, display a space in that position. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string. & Character placeholder. Display a character or nothing. If the string has a character in the position where the ampersand (&) appears, display it; otherwise, display nothing. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string. < Force lowercase. Display all characters in lowercase format. > Force uppercase. Display all characters in uppercase format. ! Force left to right fill of placeholders. The default is to fill placeholders from right to left.

Big E

[bigdoggit]

By the way, once excel has interperated 7-20 as a date, when you try to format the cell, excel still thinks it is a date. Dates are stored as serial numbers, which is what you end up seeing when it is displaying 39283.

[mikerickson]

"+" is not a string operator. It works on numbers.
"&" is the concatination operator, for use with strings.

[bigdoggit]

what you are trying to display cannot be interperated as a number. The '&' is simply a placeholder for a character. I was under the impression you wanted a string - not numbers. Am I wrong?

[bigdoggit]

did you try what I suggested by the way? Does it do what you want it to?

[bigdoggit]

Are you trying to have a formula saved to the cell, such as seven minus twenty? Is this why you want to treat everything like a number?

[mdmackillop]

You can also use the apostrophe
Range("a10").Value = "'" & Format(b, "###") + "-" + Format(c, "###")

[bigdoggit]

Interesting about the apostophe - never noticed that behavior before. One of the reasons I check theses posts - so many nuggets of info!

[bigdoggit]

why specifically does the apostrophe work? Meaning, what is the larger picture for the apostophe? Does it tell excel to put contents into a cell exactly as written?

[mdmackillop]

It's purpose is to insert text as written. eg
'=A1+B1, '7-20
and so on.
For an apostrophe to appear, use two single apostrophes.

[bigdoggit]

that's what I was guessing - good to know. I'm sure I'll find a use for that tidbit. Thanks for sharing