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

Microsoft Excel is dependent upon knowledge that’s saved accurately. In the event you obtain textual content values that must be actual numbers, use considered one of these strategies to get you again to the true work.

Numbers saved as textual content in Microsoft Excel aren’t essentially improper. As an illustration, a road handle that begins with numbers—555 City Road—is saved as textual content. However, we regularly obtain knowledge from a international supply that must be saved as a quantity, but it surely 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 suggestions each consumer ought to grasp (TechRepublic)

On this Microsoft Excel tutorial, I’ll present you 4 straightforward methods to transform numbers saved as textual content to actual numbers. All 4 strategies have their very own professionals and cons, so understanding all of them will turn out to be 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. In your comfort, you possibly can obtain the demonstration .xlsx and .xls recordsdata. The primary technique doesn’t work in Excel On-line, however the different three do.

1. Use SmartTag

Determine A exhibits a brief record of textual content values. You may inform immediately that they’re textual content as a result of the numbers are left aligned, they usually have main zeros. In the event you verify the Format setting within the Numbers group (on the Dwelling tab), you possibly can 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 deciding on 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 technique essentially the most as a result of it converts the textual content values in place. The one actual drawback is that deciding on a protracted record of values is likely to be a bit awkward, so I’ll present you a shortcut to make that simpler.

To pick the values shortly, choose the primary cell (B2) and press Ctrl + Shift+ Down Arrow. Doing so will choose all of the contiguous values. (You 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 a listing of attainable issues, as you possibly can 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 straightforward technique talked about first, you is likely to be questioning why I’m going to indicate you extra. Having the ability to choose all of the values without delay is pretty new—initially, you couldn’t convert them abruptly. So, in case you’re utilizing an older model, you want one other answer. As well as, there’s no one-size-fits-all-situations answer.

2. Textual content to Columns

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

1. Click on the Knowledge tab after which click on Textual content to Columns within the Knowledge Instruments group. Doing so launches a wizard.
2. Within the first pane, click on the Fastened Width choice—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 couple of decimal locations, which you may not need.
3. In the event you don’t need these decimal locations, select Extra Quantity Codecs from the dropdown.
4. Select Quantity within the Class record.
5. Enter 0 within the Decimal Locations choice.
6. Click on OK to format the chosen values.

Determine D

This one takes a bit of labor. You’d assume you possibly can change solely the format, proper? Sadly, it doesn’t work; you possibly can change the format, but it surely leaves the main zeros.

It’s attainable that you simply may have to retain the supply values, so the subsequent two strategies contain an expression and a operate.

3. An expression

It isn’t uncommon to want 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 equivalent to

=textvalue * 1

is useful. Determine E exhibits the results of coming into the expression

=B2*1

into cell C2 and copying it to the remaining cells.

Determine E

As you possibly can 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 couple of decimal locations, which you may not need.
3. In the event you don’t need these decimal locations, select Extra Quantity Codecs from the dropdown.
4. Select Quantity within the Class record.
5. Enter 0 within the Decimal Locations choice.
6. Click on OK to format the chosen values.

In the event you’re pondering that there must be a operate that may do each, there’s.

4. Worth()

You may skip a step through the use of the VALUE() operate as a result of it not solely removes the main zeros, however codecs the ensuing values as Normal, which has no particular format. You may go the additional step (proven above) to format the ensuing values as Quantity, however in lots of circumstances, it gained’t be vital.

Determine F exhibits the results of coming into

=VALUE(B2)

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

Determine F

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