Return to Excel Formulas List

## Assign Number Value to Text – Excel & Google Sheets

This tutorial will demonstrate how to assign a numerical value to a text string.

## VLOOKUP Method

## XLOOKUP Method

Another method is to use the XLOOKUP Function to achieve the same results:

## Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

Read our article on Locking Cell References to learn more.

## Assign Number Value to Text in Google Sheets

The examples shown above work the same way in Google Sheets as they do in Excel.

## Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

## How to Assign Value If Cell Contains Word in Excel (4 Easy Ways)

## Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

## 4 Easy Ways to Assign Value If Cell Contains Word in Excel

## 1. Combine IF and COUNTIF Functions

- First of all, prepare the data set like the following image.
- Here, I want to see through the combination formula which cells contain the specific word mentioned under the Input Criteria header in cell C4 .

=IF(COUNTIF($B7,”*”&$C$4&”*”), B7, “Not Found”)

- IF(COUNTIF($B7,”*”&$C$4&”*”), B7, “Not Found”) : Firstly, the asterisk sign (*) is a wildcard character. It searches for the “Chips” substring within cell B7 which is the “Ruffles – Chips” string.
- Secondly, the COUNTIF function returns one for every substring match. As it finds “Chips” in cell B7 , it returns 1.
- Finally, the value of the IF function is one (1)=TRUE, it returns the first argument which is the desired output.
- Thirdly, press Enter to check if the specific word matches the cell value of B5 or not.
- Then, to see all the results in the lower cells use AutoFill .

Read More: If Cell Contains Text Then Add Text in Another Cell in Excel

## 2. Merge IF, ISNUMBER, and SEARCH Functions

- Firstly, like the previous method, set input criteria in cell C4 .
- Then, based on that criteria, type the following combination formula in cell C7 of the primary data set.

=IF(ISNUMBER(SEARCH($C$4,$B7)),B7,”Not Found”)

- IF(ISNUMBER(SEARCH($C$4,$B7)),B7,”Not Found”) : Firstly, the SEARCH function searches the value of the input criteria in cell B7 . For “Chips” it returns11 which is the starting position of the substring.
- Secondly, the ISNUMBER function converts 11 into a TRUE value.
- Finally, as the IF function’s value is TRUE, it returns the first argument which is the desired output.
- Secondly, after pressing Enter , you will get the desired result based on the value of cell B7 .
- Finally, with the help of AutoFill , drag the formula to the lower cells to get those results as well.

Read More: Excel If Cell Contains Text Then Return Value (8 Easy Ways)

- How to Sum If Cell Contains Specific Text in Excel (6 Ways)
- Find text in an Excel range & return cell reference (3 ways)
- Excel Formula If Cell Contains Text Then Return Value in Another Cell

## 3. Combine IF, ISNUMBER with FIND Function

=IF(ISNUMBER(FIND($C$4,$B7)), B7, “Not Found”)

- IF(ISNUMBER(FIND($C$4,$B7)), B7, “Not Found”) : Firstly, The FIND function searches the value of the input criteria in cell B7 and returns the location. For “Chips” it returns11 which is the starting position of the substring.
- Secondly, to see the answer after using the formula press Enter .
- Then, for showing the results for the lower cells of the column drag the Fill Handle .

Read More: How to Return Value If Cells Contain Certain Text from a List

## 4. Insert VLOOKUP Function into IF and IFERROR Functions

=IFERROR(IF(VLOOKUP($C$4,$B7,1,TRUE)=”Chips”, B7, B7),”Not Found”)

- IFERROR(IF(VLOOKUP($C$4,$B7,1,TRUE)=”Chips”, B7, B7),”Not Found”) : Firstly, the VLOOKUP function looks up the criteria Chips in range cell B7 and returns the cell’s value which is Chips.
- Secondly, the IF function here always returns the value of the cell whether it is TRUE or FALSE. In this case, it returns Chips.
- Finally, as the IFERROR function’s first argument is not an error, it returns the value which is the desired output.
- Secondly, to get the result from the above formula hit the Enter
- Then, to use the formula for the rest of the cells of that column use AutoFill .

Read More: How to Use VLOOKUP If Cell Contains a Word within Text in Excel

## Things to Remember

- Be careful, while giving cell references in the formulas. You will not get a correct result with an incorrect cell reference.
- Other than the last method, the other three methods will require an exact match to find the input criteria from the cell values.

## Related Articles

- If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)
- If Cell Contains Text Then Copy to Another Sheet in Excel
- Check If Cell Contains Partial Text in Excel (5 Ways)
- Sum If a Cell Contains Text in Excel (6 Suitable Formulas)

## Chinmoy Mondol

## How to Apply Cubic Spline Interpolation in Excel (with Easy Steps)

## How to Add Text Prefix with Custom Format in Excel (4 Examples)

## How to Create Material Reconciliation Format in Excel

## How to Use VLOOKUP Function with Exact Match in Excel

Thank you so much on the Using the COUNTIF function! Can’t get easier to me.

## Leave a reply Cancel reply

Contact | Privacy Policy | TOS

## Stack Exchange Network

Connect and share knowledge within a single location that is structured and easy to search.

## How do I assign a number value to a non-numerical value in Excel

## 3 Answers 3

I would have a seperate table in another tab that details the values:

Then, in a new column, on the survey I would add this formula:

Now you should be able to sort by the new column.

- I tried this approach and it kept failing because the codes aren't in alphabetical order. – Keyslinger Apr 29, 2010 at 17:58
- 2 Did you have the last argument in the formula? the ',0)' without the quotes? The order doesn't matter if you do. – Sux2Lose Apr 30, 2010 at 15:51
- 1 =CHOOSE(A2,"VU","U","S","L","VL") – Shawn V. Wilson Jun 1, 2018 at 15:12

You can easily do this with a formula. Something like:

Where A1 is the cell containing the response.

I used Very Unlikely, Sometimes, Likely, Very Likely as possible responses.

I wound up settling on a good old-fashioned array look-up like this:

- I think you may have over-complicated this solution. – Sux2Lose Apr 30, 2010 at 15:56
- Nothing personal. I don't think it's the best answer. – Sux2Lose May 7, 2010 at 16:23
- 3 I have come to understand the inelegance of my solution and now I share your opinion that the answer you offered is superior. Thank you for downvoting me all those years ago so I could find it again today! – Keyslinger Jul 4, 2014 at 2:06

## Not the answer you're looking for? Browse other questions tagged microsoft-excel or ask your own question .

- The Overflow Blog
- How Intuit democratizes AI development across teams through reusability sponsored post
- The nature of simulating nature: A Q&A with IBM Quantum researcher Dr. Jamie...
- Featured on Meta
- We've added a "Necessary cookies only" option to the cookie consent popup

## Hot Network Questions

- What is the correct way to screw wall and ceiling drywalls?
- Why are non-Western countries siding with China in the UN?
- OK to delete Windows.db file on PC?
- What is the purpose of this D-shaped ring at the base of the tongue on my hiking boots?
- Do roots of these polynomials approach the negative of the Euler-Mascheroni constant?
- Knocking Out Zombies
- Biodiversity through radiation
- Voronoi-Lloyd ASCII art
- ncdu: What's going on with this second size column?
- Theoretically Correct vs Practical Notation
- Wahoo Kickr with 8 speed cassette?
- Why is this sentence from The Great Gatsby grammatical?
- Mathematica not solving this integral
- What's the difference between a power rail and a signal line?
- A-Z related to countries
- How do you get out of a corner when plotting yourself into a corner
- Chord III is rarely used, but Pachelbel's Canon in D has F#m
- How is Jesus "υἱὸς ὑψίστου κληθήσεται" (Luke 1:32 NAS28) different from a prophet (παιδίον, προφήτης ὑψίστου κληθήσῃ Luke 1:76 NAS28)?
- Why does hooking voltmeter to two transformers show 0 voltage?
- What video game is Charlie playing in Poker Face S01E07?
- Spacing in tuplets (in sheet-music)
- Acidity of alcohols and basicity of amines
- How does fire heat air?
- What is the point of Thrower's Bandolier?

## How to convert numbers to text using Excel TEXT function and other options

convert-number-to-text-excel-TEXT-function

## Convert number to text using the Excel TEXT function

- Add a helper column next to the column with the numbers to format. In my example, it's column D.
- Enter the formula =TEXT(C2,"0") to the cell D2 . In the formula, C2 is the address of the first cell with the numbers to convert.

- Now you need to convert formulas to values in the helper column. Start with selecting the column.
- Use Ctrl + C to copy. Then press the Ctrl + Alt + V shortcut to display the Paste Special dialog box.

The result of =TEXT(123.25,"0") will be 123.

The result of =TEXT(123.25,"0.0") will be 123.3.

The result of =TEXT(123.25,"0.00") will be 123.25.

If you need to quickly change the number to string, do it with the Format Cells… option.

## You may also be interested in

- How to extract number from string in Excel
- Excel substring: how to extract text from cell
- How to convert date to text using Excel TEXT function and no-formula ways
- Excel TEXT function with formula examples
- Convert text-formatted digits to number using Excel VALUE function and other options
- Two best ways to convert numbers to words in Excel

- Stack Overflow Public questions & answers
- Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
- Talent Build your employer brand
- Advertising Reach developers & technologists worldwide
- About the company

## Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Connect and share knowledge within a single location that is structured and easy to search.

## Assign a value for a text cell

is it possible to assign a value for a cell that contains text?

- is the number always last? – Glenn G Nov 3, 2021 at 18:44
- 1 Vlookup() or choose() come to mind. – Solar Mike Nov 3, 2021 at 18:45
- 1 Possible? Yes. How simple it is will depend on your layout. Not really sure why this would be needed, though. – Rory Nov 3, 2021 at 18:49

The following will work, but you have to type it out every time:

Now you'll be able to use Arsenal in your formulas and it will result in 7.

## Your Answer

Sign up or log in, post as a guest.

By clicking “Post Your Answer”, you agree to our terms of service , privacy policy and cookie policy

## Not the answer you're looking for? Browse other questions tagged excel or ask your own question .

- The Overflow Blog
- How Intuit democratizes AI development across teams through reusability sponsored post
- The nature of simulating nature: A Q&A with IBM Quantum researcher Dr. Jamie...
- Featured on Meta
- We've added a "Necessary cookies only" option to the cookie consent popup
- Launching the CI/CD and R Collectives and community editing features for...
- Staging Ground Beta 1 Recap, and Reviewers needed for Beta 2
- The [amazon] tag is being burninated
- Temporary policy: ChatGPT is banned

## Hot Network Questions

- Recovering from a blunder I made while emailing a professor
- Can I tell police to wait and call a lawyer when served with a search warrant?
- OIDC ID Token nonce as "poor mans digital signature"
- What video game is Charlie playing in Poker Face S01E07?
- What did Ctrl+NumLock do?
- What can a lawyer do if the client wants him to be acquitted of everything despite serious evidence?
- A limit involving the quotient of two sums
- Atheists who follow the teachings of Jesus
- Why are physically impossible and logically impossible concepts considered separate in terms of probability?
- How does fire heat air?
- Why are Suriname, Belize, and Guinea-Bissau classified as "Small Island Developing States"?
- Why is this sentence from The Great Gatsby grammatical?
- Did any DOS compatibility layers exist for any UNIX-like systems before DOS started to become outmoded?
- Biodiversity through radiation
- How can I find out which sectors are used by files on NTFS?
- Kinetic rotational energy at different rotational axis
- What is pictured in this SHERLOC camera?
- Equations in two lines
- How can I download installation file for GRASS 7.2.0?
- Does a summoned creature play immediately after being summoned by a ready action?
- Largest Binary Area
- Does Counterspell prevent from any further spells being cast on a given turn?
- Mathematica not solving this integral
- Wahoo Kickr with 8 speed cassette?

- Kutools for Excel
- Kutools for Word
- Kutools for Outlook
- How to Install or Uninstall
- End User License Agreement
- Tips & Tricks for Excel
- Tips & Tricks for Word
- Tips & Tricks for Outlook
- Excel Functions
- Excel Formulas
- Excel Charts
- Outlook Tutorials
- Search Search more
- About Us Our Team
- User Testimonials Users Say
- Retrieve License Lost License?
- Report a Bug Bug Report
- Forum Post in Forum
- Contact Us Talk to us via email
- Forgot Username?
- Forgot Password?

## How to assign a value or category based on a number range in Excel?

## Assign a value or category based on a number range with formulas

## Formula 1: Using a formula with the combination of the IF function and the AND function

## Formula 2: Using VLOOKUP function to get it done

VLOOKUP function can help to handle this problem as well.

Click here to know more about the VLOOKUP function.

Note: In the formula, $E$2:$F$5 is the table range we created in step 1.

## Related articles:

- How to move entire row to another sheet based on cell value in Excel?
- How to calculate average in a column based on criteria in another column in Excel?
- How to automatically send email based on cell value in Excel?
- How to sum values based on selection of drop-down list in Excel?

## The Best Office Productivity Tools

Kutools for excel solves most of your problems, and increases your productivity by 80%.

- Reuse : Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range ...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns ... Prevent Duplicate Cells; Compare Ranges ...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets , Check Boxes and more...
- Extract Text , Add Text, Remove by Position, Remove Space ; Create and Print Paging Subtotals; Convert Between Cells Content and Comments ...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets ; Merge Tables based on key columns; Split Data into Multiple Sheets ; Batch Convert xls, xlsx and PDF ...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name ...
- More than 300 powerful features . Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

## Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

- Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

## IMAGES

## VIDEO

## COMMENTS

This tutorial will demonstrate how to assign a numerical value to a text string. assign number value to text Main. VLOOKUP Method. To assign a number value

Hello, I'm making a pricing spreadhseet and I need to assign a numeric value to a text a cell which can be selected from a multiple option

... file here: https://www.bluepecantraining.com/sum-or-average-text-values-like-numbers-in-excel-assign-numeric-value-to-text-and-sum-or-a.

Select the cell or column that contains the text you want to split. · Select Data > Text to Columns. · In the Convert Text to Columns Wizard, select Delimited >

How to Assign Value to Text and Sum in Excel (2 Easy Methods) · Assign Value to Texts.xlsx · =SUM(COUNTIF(C5:E5,$H$5:$H$7)*$I$5:$I$7) · =SUM(INDEX($I$5:$I$7,N(IF(1

4 Easy Ways to Assign Value If Cell Contains Word in Excel · =IF(COUNTIF($B7,"*"&$C$4&"*"), B7, "Not Found") · =IF(ISNUMBER(SEARCH($C$4,$B7)),B7,"

I would have a seperate table in another tab that details the values: Code Value VU 1 U 2 S 3 L 4 VL 5. Then, in a new column, on the survey

Select the range with the numeric values you want to format as text. Right click on them and pick the Format Cells… option from the menu list.

It sounds like what you want is a defined name. Just go to the formulas ribbon and click define name. Then Type in the name of the Team and

1. Select a blank cell, enter the following formula and press the Enter key. Select the result cell, drag its AutoFill Handle down to the cells where you want