User 12033439342474713344
Original Poster
Oct 24, 2023

How do you convert text in one cell to a corresponding numerical value in another cell

How do you convert text in one cell to a corresponding numerical value in another cell when the text could be several different choices. In this case there is a sleep quality description - "Very Poor", "Poor" to "Very Good" that needs to convert to a corresponding numerical value like "Very Poor" = "1". I've been able to do one but not the whole series of descriptions using =IF(J14="Fair to Good",5). I can't determine how to include all of the other possibilities in the range of descriptions and corresponding numerical values so as to repeat the formula down a column (each row represents a different date).

Details
Sheets,Chrome OS,Personal use
Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
I duplicated the tab and added my solution.

I cleared all the original formulas in C2:C32

I then entered the following single formula in cell C1 only


=VSTACK("SLEEP VALUE", MAP(B2:B32, LAMBDA(sleepQuality, SWITCH(sleepQuality, "Very Poor", 1, "Poor", 2, "Poor to Fair", 3, "Fair", 4, "Fair to Good", 5, "Good", 6, "Very Good", 7, IFERROR(1/0)))))


I also widened column B so you can read the full words from the dropdown.


I hope this helps,

Ben Liebrand
Last edited Oct 24, 2023
Original Poster User 12033439342474713344 marked this as an answer
Helpful?
All Replies
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
Hi, User 12033439342474713344,

There are a number of approaches to this but to offer the best solution it would hw helpful for you to share an example mockup spreadsheet demonstrating what you are trying to do.

 
For me or other forum contributors to help (if I'm not around), I suggest you share an editable COPY of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do!  Here are some tips to help you get a great answer quickly... Product Experts TIPS FOR SHARING
 
***** IMPORTANT: Read the Tips post above, then check your sharing settings before posting - you want "Anyone with the link can Edit"
 
 
 
Ben Liebrand

Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
Just taking a wild guess you can try the following as a guideline

=SWITCH(J14,  "Very Poor", 1, "Poor", 2, "Fairly Good", 3, "Good", 4, "Very Good", 5, IFERROR(1/0))

You can alster the text and values to suit you need.

I hope this helps,

Ben Liebrand

User 12033439342474713344
Original Poster
Oct 24, 2023
Ben,
Thanks, this works perfectly! To improve data entry efficiency, could I connect my description column (Very Poor, Poor, etc.) to a drop down list?

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
Hi, User 12033439342474713344,

If you are referring to Column J then the answer is yes.

Ben Liebrand

When you've received a response that answers your question, please observe these forum courtesies;
 • Leave your demo sheet shared as part of this solution's archive,
 • Click Recommend on the post that best addressed your question, and
 • Post again soon!

Last edited Oct 24, 2023
User 12033439342474713344
Original Poster
Oct 24, 2023
Thanks again, Ben! I've created a sample sheet with your solution:
User 12033439342474713344
Original Poster
Oct 24, 2023
...Here's the sample sheet with your solution, Ben:

I would like to add a drop down in Column B but I've run into what might just be a formatting issue? When I use the drop down I'd like the answer to clearly appear versus remaining as a partially visible choice in the drop down.

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
The link you have shared does not seem to be valid.

When I click on the link it says "Blocked"
User 12033439342474713344
Original Poster
Oct 24, 2023
I've indicated that the share on the sheet would be anyone with the link. I assume that is correct?

Now?

G
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
I am not sure I understand what you are getting. At guess, it sounds like you want the dropdown selection to be replaced with the number value for that dropdown. If that is what you are asking then that is not possible. A dropdown and formula cannot share the same cell. The way you have it now is the accepted approach. And that is how I would do it. However, I would reconstruct the formula in column C.

Let me know what you really are getting at so I can have a better idea of what your needs are.
Last edited Oct 24, 2023
User 12033439342474713344
Original Poster
Oct 24, 2023
Thanks. I'm OK with the presentation of Column B but I was hoping for a cleaner one-word result. So if I chose the quality as "Very Poor" that is all I'd see in the cell. But, with the color coding it does convey the meaning. The numbers in Column C - your formula - is probably where the most value is in terms of understanding the person's "rating" of their sleep quality, especially in computing an average and, perhaps a standard deviation, as the month concludes.

How would you reconstruct your formula in Column C? Curious.

Thanks for all your help!

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
Before I offer a new formula, are you wanting to make use of the Sleep Rating table in columns D & E instead of incorporating it in the formula I originally offered?

Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
Also, you need to change the example spreadsheet mode from View Only mode to anyone with the link can edit. That way I can add my solution to your example spreadsheet.
User 12033439342474713344
Original Poster
Oct 24, 2023
The additional columns were for a reference only. They don't need to be used. Deleting. I've adjusted the share to anyone with link / editor.
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Recommended Answer
Oct 24, 2023
I duplicated the tab and added my solution.

I cleared all the original formulas in C2:C32

I then entered the following single formula in cell C1 only


=VSTACK("SLEEP VALUE", MAP(B2:B32, LAMBDA(sleepQuality, SWITCH(sleepQuality, "Very Poor", 1, "Poor", 2, "Poor to Fair", 3, "Fair", 4, "Fair to Good", 5, "Good", 6, "Very Good", 7, IFERROR(1/0)))))


I also widened column B so you can read the full words from the dropdown.


I hope this helps,

Ben Liebrand
Last edited Oct 24, 2023
Original Poster User 12033439342474713344 marked this as an answer
User 12033439342474713344
Original Poster
Oct 24, 2023
Ben,
This has been terrific! Thank you. Just so you know all of this effort is a collaboration with my 91-year old father, my brother and I as we help him convert his medical data (there are some other pieces of the puzzle) into a form that he can better see the big picture, trends, and share with his doctors. He sends us the raw information daily then we key it in the sheet then he prints as needed for medical appointments. Thanks for sharing your talents with our family!

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
Hi, User 12033439342474713344,

 
You are welcome and thank you for your feedback.
 
I'm glad I was able to help.

You can see why sharing an example mockup spreadsheet is so important when asking for help in the forum. This allowed me to see what you were trying to do and allowed me to construct a more efficient formula to accommodate what was needed.

Also, you will notice I removed the cartridge return you had in cell C1 so the two words are side by side and then applied word wrap to the cell so the two words would display the same way if the column width was made narrower.
 
Ben Liebrand

Last edited Oct 24, 2023
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 24, 2023
When you've received a response that answers your question, please observe these forum courtesies;
 • Leave your demo sheet shared as part of this solution's archive,
 • Click Recommend on the post that best addressed your question, and
 • Post again soon!
User 12033439342474713344
Original Poster
Oct 25, 2023
Thanks, again. And thanks for the additional help to improve the presentation.

I appreciate the guidance on the posting protocol, too.

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 25, 2023
Hi, User 12033439342474713344,

Okay, but the thing is, you still have not marked the appropriate solution offered as the recommended solution. Once you have done so your post will show that it was successfully been resolved and other readers will then be able to identify this as a reason to view your post and also learn from that. You will notice the solution offered as a Recommend button under it.

Ben Liebrand
User 12033439342474713344
Original Poster
Oct 26, 2023
Thanks. I saw the instruction to  "mark the appropriate solution offered as the recommended solution." But I couldn't tell whose responsibility it was to do so. And...I'm not seeing where to do that. I see "Is this a helpful answer". Do I just click "yes" in the latest post? Do I scroll up to the post where we concluded that we had resolved? Or is there somewhere else I need to look for and indicate the "...recommended solution."?

G
User 12033439342474713344
Original Poster
Oct 26, 2023
Ben,
See my last reply.

Thanks for your patience. I know enough about Sheets / Excel to be dangerous.

When I paste the formula into the actual sheet I'm using to track data I saw that I would need to change the columns in the "MAPB2:B32" but I'm getting a REF error and it eliminates my header title. Is there anything else I need to change about the formula?

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 26, 2023
Hi, User 12033439342474713344,

I can't tell from your description what the issue is. The only thing I can say is that the #REF! error is telling you that the formula is attempting to to overwrite other data in its path. The #REF! error tells you what data the formula is attempting to overwrite.


As to the Recommend issue. Each comment in your post has a Recommend button. Which ever comment is the correct answer is the button you press. This will then show other readers which is the correct answer. I hope that makes sense. If you look at other posts that have a Recommended answer than you can see how it was done.

Ben Liebrand
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 26, 2023
In any case,  the syntax must be in the following format

MAP(B2:B32,

not

MAPB2:B32,

Remember, I can't see what you are doing. You must use the correct column range to suite what is needed.

Ben Liebrand
User 12033439342474713344
Original Poster
Oct 27, 2023
Thanks. I've been able to replicate the formula correctly in the actual data sheet.

I'm now working on finding the recommendation post.

George
User 12033439342474713344
Original Poster
Oct 27, 2023
Just for future clarity. There is a option to mark an answer as helpful, yes or no. Which I did on what I thought was the post that provided the solution. It is only then that I can see that the post is "recommended".

Please let me know if this is the correct way to recommend and also if you think I've indicated the best post.

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 27, 2023
When a post thread starts to becoming too long then some of the comments start to rollup and hidden. Thy will look something like this




Keep clicking on that blue link to unfold the other hidden comments until you find the one you are looking for.

I hope that makes sense.

Ben Liebrand

User 12033439342474713344
Original Poster
Oct 27, 2023
Yes, I think I found the right one. I hope you agree so let me know if another is better for the purposes of the community.

George
Ben Liebrand
Diamond Product Expert
Docs Editor, Google Sheets
Oct 27, 2023
By the way, you did click on Reccommend on onr of the coomments :-)

Thank you,

Ben Liebrand
false
5945562564816092911
true
Search Help Center
true
true
true
true
true
35
Search
Clear search
Close search
Main menu
false