Write a number in a cell as text
 

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

Write a number in a cell as text
 

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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533

Write a number in a cell as text
 

in vloolup use

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

--
Regards,
Tom Ogilvy

"Antonio" wrote in message
...
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

Write a number in a cell as text
 

One way:

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


In article ,
Antonio wrote:

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

Write a number in a cell as text
 

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

"tony h" wrote:


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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533

Write a number in a cell as text
 

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

"tony h" wrote:


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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533



--

Dave Peterson

Write a number in a cell as text
 

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").Activa te

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" wrote:

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

"tony h" wrote:


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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533



--

Dave Peterson

Write a number in a cell as text
 

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



Antonio wrote:

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").Activa te

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" wrote:

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

"tony h" wrote:


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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533



--

Dave Peterson


--

Dave Peterson

Write a number in a cell as text
 

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

"Dave Peterson" wrote:

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



Antonio wrote:

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").Activa te

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" wrote:

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

"tony h" wrote:


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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533



--

Dave Peterson


--

Dave Peterson

Write a number in a cell as text
 

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

"Antonio" wrote:

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

"Dave Peterson" wrote:

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



Antonio wrote:

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").Activa te

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" wrote:

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

"tony h" wrote:


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

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=546533



--

Dave Peterson


--

Dave Peterson

Write a number in a cell as text
 

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.


In article ,
Antonio wrote:

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