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

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

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

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

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

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

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

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