Write a number in a cell as text

[Guest]

Line 1: Cells(1,1)=ticker

enters the value of ticker in cell A1.

If ticker = "1" AND format of A1 is General

line 1 results in A1=1 as a number NOT as text, even though ticker is a string

Is there a way to force the writing of ticker in A1 as text, WITHOUT
CHANGING THE FORMAT OF A1 TO TEXT?

This is quite relevant for me because the VLOOKUP function won't work
otherwise and the spreadsheet where A1 is located cannot be modified.

I have tried CStr() but makes no difference. In a General format cell Excel
changes a numeric string to a number.

Thanks,

Antonio

 

[tony h]

prefix with a single quote ="'" & cstr(1)

regards

 

[Tom Ogilvy]

in vloolup use

=vlookup(Text(A1,"@"),rng,2,false)

 

[JE McGimpsey]

One way:

Cells(1, 1).Value = "'" & ticker

 

[Guest]

Thank you for your suggestions, but they won't work.

Excel will keep considering it a number and VLOOKUP won't find it.

Any comments?

Thanks,

Antonio

 

[Dave Peterson]

Are you sure?

I'd try it once more.

Thank you for your suggestions, but they won't work.

Excel will keep considering it a number and VLOOKUP won't find it.

Any comments?

Thanks,

Antonio

 

[Guest]

I am pretty sure.

I understand the logic of the "" & A1

In fact I even went as far as adding a " " and then removing it.

The relevant VBA code is:

ticker = UCase(Range("trimmed_ticker"))

Workbooks("TB_API.xls").Worksheets("SETUP").Activate

Cells(6 + order_number, 9) = action
Cells(6 + order_number, 10) = "" & ticker


In the master sheet, the (simplified) problem LOOKUP is:


=VLOOKUP(trimmed_ticker,[TB_API.xls]SETUP!$J$7:$AQ$42,31,FALSE)

 

[Dave Peterson]

There was an apostrophe in that suggestion:
"'"
not just
""

I am pretty sure.

I understand the logic of the "" & A1

In fact I even went as far as adding a " " and then removing it.

The relevant VBA code is:

ticker = UCase(Range("trimmed_ticker"))

Workbooks("TB_API.xls").Worksheets("SETUP").Activate

Cells(6 + order_number, 9) = action
Cells(6 + order_number, 10) = "" & ticker

In the master sheet, the (simplified) problem LOOKUP is:

=VLOOKUP(trimmed_ticker,[TB_API.xls]SETUP!$J$7:$AQ$42,31,FALSE)

 

[Guest]

Got it!.

Thank you for your patience.

It has been one of those things where one gets stuck, knowing you are
missing something very simple.

I think years ago I learned that the apostrophe forces the number as text.
But I had no memory of that.

Is that only in Excel or is it a convention in most programming languagues?

Many thanks,

Antonio

There was an apostrophe in that suggestion:
"'"
not just
""

I am pretty sure.

I understand the logic of the "" & A1

In fact I even went as far as adding a " " and then removing it.

The relevant VBA code is:

ticker = UCase(Range("trimmed_ticker"))

Workbooks("TB_API.xls").Worksheets("SETUP").Activate

Cells(6 + order_number, 9) = action
Cells(6 + order_number, 10) = "" & ticker

In the master sheet, the (simplified) problem LOOKUP is:

=VLOOKUP(trimmed_ticker,[TB_API.xls]SETUP!$J$7:$AQ$42,31,FALSE)

Are you sure?

I'd try it once more.

Antonio wrote:

Thank you for your suggestions, but they won't work.

Excel will keep considering it a number and VLOOKUP won't find it.

Any comments?

Thanks,

Antonio

:


prefix with a single quote ="'" & cstr(1)

regards

 

[Guest]

Perhaps someone can explain.

Does the addition of the aposthrophe at the beginning change anything else?

I have been testing and seems to be working fine with numeric strings and
with text strings.

However, am I not adding an additional character? Where does it go?

Is this a convention that says that "'" & numeric string is, by definition,
= numeric string as text. AND that says that "'" & text string is, by
definition, = text string

It just does not look very safe and certainly it is not obvious.

Thanks,

Antonio

Got it!.

Thank you for your patience.

It has been one of those things where one gets stuck, knowing you are
missing something very simple.

I think years ago I learned that the apostrophe forces the number as text.
But I had no memory of that.

Is that only in Excel or is it a convention in most programming languagues?

Many thanks,

Antonio

There was an apostrophe in that suggestion:
"'"
not just
""

I am pretty sure.

I understand the logic of the "" & A1

In fact I even went as far as adding a " " and then removing it.

The relevant VBA code is:

ticker = UCase(Range("trimmed_ticker"))

Workbooks("TB_API.xls").Worksheets("SETUP").Activate

Cells(6 + order_number, 9) = action
Cells(6 + order_number, 10) = "" & ticker

In the master sheet, the (simplified) problem LOOKUP is:

=VLOOKUP(trimmed_ticker,[TB_API.xls]SETUP!$J$7:$AQ$42,31,FALSE)

:

Are you sure?

I'd try it once more.

Antonio wrote:

Thank you for your suggestions, but they won't work.

Excel will keep considering it a number and VLOOKUP won't find it.

Any comments?

Thanks,

Antonio

:


prefix with a single quote ="'" & cstr(1)

regards

 

[JE McGimpsey]

the apostrophe is a flag to let XL know that what follows is text. The
apostrophe is not evaluated as part of the cell's contents.

It's a long-standing XL convention and it's safe.