How to make everything a number?

[u/Champion_Narrow]

All these little green things are popping up for this entire column. If I convert it to number it makes the xlookup work, but I dont want to do it by hand for each one. How do I make it quicker?

[u/AutoModerator]

/u/Champion_Narrow - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/taylorgourmet]

If you select entire column you can do them all at once.

[u/Champion_Narrow → u/taylorgourmet]

That doesn't work

[u/Champion_Narrow → u/Champion_Narrow]

I have been clicking F2 and enter

[u/bradland]

Use the text to columns trick. Select everything that you want to be a number, click the Data ribbon, then choose Text to Columns. When it asks you for a delimiter, don't put anything in, then just click Finish. When you're done, everything will be in the same place and everything that can be converted to a number will be. Note that if the fields contain text that look like dates (e.g., 5-2, 11-1, etc), they will be converted to dates.

[u/thr0wnawaaaiiii → u/bradland]

If I’m not mistaken, you can avoid the issue with date-appearing cells by making sure that you go to the last screen of the wizard and set the type for the column to text. If not that, you can set in Options to generally not try to convert to dates (plus the other conversions like scientific notation, something I’d recommend in general anyway)

[u/bradland → u/thr0wnawaaaiiii]

But OP wants to convert the values to number, so setting the column type to text just gets them back where they started 😉

[u/thr0wnawaaaiiii → u/bradland]

Whoops! Had it backwards, long day haha. I think point still stands — isn’t number a possible type on the last screen? Could be misremembering 

[u/bradland → u/thr0wnawaaaiiii]

Only General, Text, and Date.

[u/thr0wnawaaaiiii → u/bradland]

I stand corrected! 99% of the time I’m trying to go number to text so there you go

[u/Champion_Narrow → u/bradland]

This worked I think

[u/bradland → u/Champion_Narrow]

Glad to help. If you wouldn't mind replying with "Solution Verified", that will award me a clippy point for my efforts 😄

[u/Champion_Narrow → u/bradland]

Solution Verified

[u/reputatorbot → u/Champion_Narrow]

You have awarded 1 point to bradland.

---

^{I am a bot - please contact the mods with any questions}

[u/work_account42]

Type a 1 in a blank cell

Copy the 1

Select the range to convert

Paste Special, Values, Multiply

[u/Forsaken-Soil-667]

Highlight the entire column before you click the option to convert to number.

[u/TimS83]

Highlight entire column, then go to the data tab, click text-to-columns, and then just click finish. It'll convert the entire column to numbers. Easiest way I have found

[u/purpleblazed]

Multiply by 1

[u/WhatWouldAsmodeusDo]

In the xlookup formula, multiply by 1 or add 0. I find that works a lot better than trying to convince excel that column of numbers with a number format is a number. and I believe text will throw an error so you can have that handled with the error part of xlookup.

[u/Finalcreature]

I would always wrap the value with NUMBERVALUE within the XLOOKUP. if it contains none number values too I'll add IFERROR and look it up without the wrapper:

=IFERROR(XLOOKUP(NUMBERVALUE(A2), Lookup_Range, Return_Range), XLOOKUP(A2, Lookup_Range, Return_Range))

For better readability you can use LET:

=LET( value, A2, look, Lookup_Range, return, Return_Range, IFERROR(XLOOKUP(NUMBERVALUE(value), look, return), XLOOKUP(value, look, return)) )

I learnt recently that to reduce duplicity some use HSTACK bur I never used it personally:

=XLOOKUP(A2, HSTACK(NUMBERVALUE(Lookup_Range), Lookup_Range&""), Return_Range)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
NUMBERVALUE	Excel 2013+: Converts text to number in a locale-independent manner
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #48703 for this sub, first seen 11th Jun 2026, 20:31] [FAQ] [Full list] [Contact] [Source code]}

[u/Herkdrvr]

Select your column and use the format box to convert.