Converting a series of numbers to text format

[DashboardNovice]

In the attached file I have numbers in column D as number format. In column C, I verified this with ISNUMBER( ).

1. To convert these numbers to a text format, I selected D5:D13, then I selected Text from the drop-down menu:



2. After doing this, the numbers become left-aligned which seems to indicate that they are now in Text format (see column H). However, the ISNUMBER( ) in column G indicates they are still in number format which I believe they are.

The reason why I say this is because if I select cell H5, then press F2 (Edit Mode), then press Enter, the formula in G5 now evaluates to FALSE indicating that the number in H5 is now in Text format. So far so good.

I don't want to do this F2 thing for every number in column H, because what if I had hundreds of numbers? It would take too long.

What if I selected H5:H13, then pressed F2, then pressed Enter? If I do that, all the cells are now converted to Text format, but the problem is that the number in H5 copies all the way down which I don't want.

After selecting the original numbers (which start in number format), then selecting Text from the drop-down menu (point #1), how can I convert all of these numbers to Text format?

 

[Belleke]

What is the reult you want? Example?

 

[vletm]

DashboardNovice​

Why do You want to convert numbers to ... text?
If for some reason ...
could You use something like ...
to cell K5 =TEXT(D5:D13,"@")
to cell J5 =ISNUMBER(K5#)

 

[DashboardNovice]

DashboardNovice​

Why do You want to convert numbers to ... text?
If for some reason ...
could You use something like ...
to cell K5 =TEXT(D5:D13,"@")
to cell J5 =ISNUMBER(K5#)

Yes, this is the result I was looking for in column K. I forgot about the TEXT( ). I am not familiar with the "@" argument. I will have to look into that. Also, for ISNUMBER(K5#), I'm not sure what the # sign does. I will have to look into that too.

Thank you for help with this.

Edit 1: What does the "@" sign do in the TEXT( )? I can't find any info on that.
Edit 2: What does the "#" sign do in the ISNUMBER( )? I thought all you would need is a cell reference, then you can copy the formula down.

 

[vletm]

DashboardNovice​

You skipped my why-question ... why did You do that?
After that, I could read Your what-questions.

 

[DashboardNovice]

DashboardNovice​

You skipped my why-question ... why did You do that?
After that, I could read Your what-questions.

I had a situation at work where a list of contract numbers downloaded as text and numbers. Not only that some contract numbers are alpha numeric. So to do a VLOOKUP, I should have everything in text format.

 

[vletm]

"@" ( as well as "0") is format_text -option
"#"

Others who uses formulas would explain with words.

So You're cleaning and cleaning ...

I would use as written
Note: The TEXT function converts numbers to text, which may make it difficult to reference in later calculations. It’s best to keep your original value in one cell, and then use the TEXT function in another cell. Then, if you need to build other formulas, always reference the original value and not the TEXT function result.
VLOOKUP works with all formats

 

[AliGW]

I'll try and address the issue you have directly.

Using formatting does NOT change the underlying value of a cell; it just changes how it looks. So you need a different approach.

In the attached, you will see that I have set up a lookup table with numbers converted to text in L5:M13.

In D5:D13 you have the same numbers, but as real numbers.

This will return #N/A:

=VLOOKUP(D5,$L$5:$M$13,2,0)

However, this will return the correct value:

=VLOOKUP(TEXT(D5,"00000"),$L$5:$M$13,2,0)

I would recommend this approach in your case.

I hope you find this helpful.

 

[AliGW]

By the way, if the situation is the other way around, you can use this (see attached):

=VLOOKUP(--D15,$O$5:$P$13,2,0)

Hopefully between this and post #8 you'll find a way forward. Let me know if you need any further explanation.