4 fast and simple methods to transform numbers saved as textual content to actual numbers in Excel

Microsoft Excel is determined by knowledge that’s saved accurately. When you obtain textual content values that must be actual numbers, use one in every of these strategies to get you again to the actual work.

Numbers saved as textual content in Microsoft Excel aren’t essentially fallacious. As an illustration, a avenue tackle that begins with numbers—555 City Road—is saved as textual content. Alternatively, we frequently obtain knowledge from a international supply that must be saved as a quantity, nevertheless it saved as textual content on the supply. When this occurs, we should convert that textual content into numbers earlier than we are able to use the info.

SEE: 83 Excel ideas each consumer ought to grasp (TechRepublic)

On this Microsoft Excel tutorial, I’ll present you 4 simple methods to transform numbers saved as textual content to actual numbers. All 4 strategies have their very own professionals and cons, so realizing all of them will come in useful.

I’m utilizing Microsoft 365 on a Home windows 10 64-bit system. I like to recommend that you simply maintain off on upgrading to Home windows 11 till all of the kinks are labored out. To your comfort, you’ll be able to obtain the demonstration .xlsx and .xls recordsdata. The primary methodology doesn’t work in Excel On-line, however the different three do.

1. Use SmartTag

Determine A exhibits a brief checklist of textual content values. You possibly can inform instantly that they’re textual content as a result of the numbers are left aligned, they usually have main zeros. When you examine the Format setting within the Numbers group (on the Dwelling tab), you’ll be able to see that these values are textual content and never saved as actual numbers. I set the Textual content format myself to simulate the issue. You are able to do so by choosing a small vary and select Textual content from the Quantity Format dropdown within the Quantity group.

Determine A

For my cash, I just like the SmartTag methodology essentially the most as a result of it converts the textual content values in place. The one actual drawback is that choosing a protracted checklist of values may be a bit awkward, so I’ll present you a shortcut to make that simpler.

To pick out the values shortly, choose the primary cell (B2) and press Ctrl + Shift+ Down Arrow. Doing so will choose all of the contiguous values. (You will need to accommodate any blanks within the vary.)

With the values chosen, click on one of many SmartTag indicators—the inexperienced triangle within the top-left nook of the cell—and do the next:

1. Click on the ensuing SmartTag (if the SmartTag is already seen, click on it with out clicking the triangle). Doing so shows an inventory of doable issues, as you’ll be able to see in Determine B. These SmartTags are certainly “good.” The primary merchandise tells you that the quantity is saved as textual content—that’s the issue. The subsequent merchandise is the answer: Convert to Quantity.
2. Choose the second merchandise, Convert to Quantity, to immediately convert all chosen values to actual numbers, and in place, as proven in Determine C.

Determine B

Determine C

For higher or worse, the conversion converts the values to Normal, not Quantity.

With this simple methodology talked about first, you may be questioning why I’m going to indicate you extra. Having the ability to choose all of the values directly is pretty new—initially, you couldn’t convert them suddenly. So, if you happen to’re utilizing an older model, you want one other resolution. As well as, there’s no one-size-fits-all-situations resolution.

2. Textual content to Columns

The Textual content to Columns function is extremely simple. It leaves the values in place and removes the main zeros, however the values stay textual content, so you need to reset the format. Choose the values as you probably did earlier than after which do the next:

1. Click on the Information tab after which click on Textual content to Columns within the Information Instruments group. Doing so launches a wizard.
2. Within the first pane, click on the Fastened Width possibility—there aren’t any delimiters.
3. Click on End—it’s that fast.

Sadly, the values are nonetheless saved as textual content. With the values chosen do the next:

1. Click on the Dwelling tab.
2. From the Quantity Format dropdown (within the Quantity group), select Quantity, as proven in Determine D. This one tacks on a number of decimal locations, which you may not need.
3. When you don’t need these decimal locations, select Extra Quantity Codecs from the dropdown.
4. Select Quantity within the Class checklist.
5. Enter 0 within the Decimal Locations possibility.
6. Click on OK to format the chosen values.

Determine D

This one takes a bit of labor. You’d suppose you possibly can change solely the format, proper? Sadly, it doesn’t work; you’ll be able to change the format, nevertheless it leaves the main zeros.

It’s doable that you simply may must retain the supply values, so the following two strategies contain an expression and a perform.

3. An expression

It isn’t uncommon to wish to retain the supply knowledge. When that’s the case, you gained’t need to convert the unique values in place. That’s when a easy expression reminiscent of

=textvalue * 1

turns out to be useful. Determine E exhibits the results of getting into the expression

=B2*1

into cell C2 and copying it to the remaining cells.

Determine E

As you’ll be able to see, the ensuing values don’t have any main zeros, however are nonetheless saved as textual content, so do the next to alter the format to Quantity:

1. Click on the Dwelling tab.
2. From the Quantity Format dropdown (within the Quantity group), select Quantity. This one tacks on a number of decimal locations, which you may not need.
3. When you don’t need these decimal locations, select Extra Quantity Codecs from the dropdown.
4. Select Quantity within the Class checklist.
5. Enter 0 within the Decimal Locations possibility.
6. Click on OK to format the chosen values.

When you’re pondering that there must be a perform that may do each, there’s.

4. Worth()

You possibly can skip a step by utilizing the VALUE() perform as a result of it not solely removes the main zeros, however codecs the ensuing values as Normal, which has no particular format. You possibly can go the additional step (proven above) to format the ensuing values as Quantity, however in lots of circumstances, it gained’t be obligatory.

Determine F exhibits the results of getting into

=VALUE(B2)

it to C2 and copying the perform to the remaining cells.

Determine F

These 4 strategies present fast and simple methods to transform textual content to actual numbers. Figuring out your knowledge will enable you resolve which to make use of. You may use all of them at a while or one other.