PDA

View Full Version : Force Excel to Stop Recognizing Numbers



sam314159
05-20-2010, 05:20 AM
I have the following VBA code in a macro:



Dim someValue As String

someValue = "4"

Range(A1).Select

ActiveCell.FormulaR1C1 = someValue



I want to enter that someValue, 4, into the cell as PURE text. I don't want Excel to give me that stupid "Number stored as text error". That messes me up because the spreadsheet has to later be processed by label printing software and the software needs pure text only.

I have tried



someValue = CStr(4)



And it didn't work. The only thing that worked so far, is something cheesy like:

someValue = "Number 4".

Is there any way to make someValue = 4 and still store it as a pure string?

PS The cell is already formatted as a 'Text'.

lynnnow
05-20-2010, 06:02 AM
Have you tried putting an apostrophe before the number when inserting it in the workbook?

for example: "'4" or something like that.

Also, posting your workbook would be beneficial in understanding where the values are coming from.

sam314159
05-20-2010, 06:07 AM
Yes, I have tried that and it worked, but the apostrophe also shows which makes the output look kinda bad.

I was just able to work around it by entering an invisible character before the 4 by using Alt + 255. So someValue now equals " 4".

What I'd still like to know please is:

1. Can anyone think of a more graceful solution?

2. What is the etiquette on using an invisible character? It sounds kind of shady to me lol. It certainly fixes the problem for now, I am just worried it will break something else in the future. Any ideas?

GTO
05-20-2010, 08:09 AM
Did you already change the cells' number format to Text?