[string] how to write value into cell with vba code without auto type conversion?

This problem seems very simple, yet I just can not find the solution (I am already loosing my mind about it :) )

OK, so I just want to put a certain value into an excel cell, using vba code, just as simple as this:

Cells(1,1).Value2 = "123,456"

The problem: this is a string (intentionally), but excel always convert it to number, and put that number into the cell, instead of the string that I wanted.

How can I force excel not to convert it, and just put into the cell exactly what I want (the string)??

Thanks,

This question is related to string excel vba type-conversion

The answer is


Cells(1,1).Value2 = "'123,456"

note the single apostrophe before the number - this will signal to excel that whatever follows has to be interpreted as text.


Indeed, just as commented by Tim Williams, the way to make it work is pre-formatting as text. Thus, to do it all via VBA, just do that:

Cells(1, 1).NumberFormat = "@"
Cells(1, 1).Value = "1234,56"

This is probably too late, but I had a similar problem with dates that I wanted entered into cells from a text variable. Inevitably, it converted my variable text value to a date. What I finally had to do was concatentate a ' to the string variable and then put it in the cell like this:

prvt_rng_WrkSht.Cells(prvt_rng_WrkSht.Rows.Count, cnst_int_Col_Start_Date).Formula = "'" & _ 
    param_cls_shift.Start_Date (string property of my class) 

Examples related to string

How to split a string in two and store it in a field String method cannot be found in a main class method Kotlin - How to correctly concatenate a String Replacing a character from a certain index Remove quotes from String in Python Detect whether a Python string is a number or a letter How does String substring work in Swift How does String.Index work in Swift swift 3.0 Data to String? How to parse JSON string in Typescript

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to type-conversion

How can I convert a char to int in Java? pandas dataframe convert column type to string or categorical How to convert an Object {} to an Array [] of key-value pairs in JavaScript convert string to number node.js Ruby: How to convert a string to boolean Convert bytes to int? Convert dataframe column to 1 or 0 for "true"/"false" values and assign to dataframe SQL Server: Error converting data type nvarchar to numeric How do I convert a Python 3 byte-string variable into a regular string? Leading zeros for Int in Swift