EXCEL 365 & USING TEXT

[cindyldeleonoptum]

Can you please advise, when using the TEXT function formula, on a number, an  Ampersand generates allowing V-Lookup function on other Ampersand text cells. 

However, when using the TEXT selection, within the number box, an Ampersand does not generate, nor can a V-Lookup be used on Ampersand text cells. 

What is the difference with using the "Text" Number box versus using the Text function with 365?  We don't seem to be changing the cell to text when using the

Number box?

Thank you kindly for your assistance. 

[#2]

@cindyldeleonoptum Not sure what you mean. The ampersand doesn't just appear. You have to type it into a formula and it causes numbers and/or texts to be joined as one. The end result becomes a text.

For instance, ="10"&"00" will display 1000. But it will be a text. Say this text is in A1. If you then use the TEXT function like:

=TEXT(A1,"$ #,###.00")

you can make it look like $ 1,000.00, but it will still be a text.

Please clarify what you have in mind and how you want to relate this to using VLOOKUP.

[#3]

@cindyldeleonoptum 

Ampersand is used when you'd like to manually insert the text into the cell independently of such cell format (General, Number, etc), other words you explicitly indicate such value shall be considered as text.

When you return text into such cell by formula ampersand is not generated, that's implicit indication of text in such case.