Oct 24, 2023
How do you convert text in one cell to a corresponding numerical value in another cell
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Oct 24, 2023
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?Upvote Downvote
All Replies
Oct 24, 2023
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
Oct 24, 2023
=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
Oct 24, 2023
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
Oct 24, 2023
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
Oct 24, 2023
Oct 24, 2023
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
Oct 24, 2023
When I click on the link it says "Blocked"
Oct 24, 2023
Now?
G
Oct 24, 2023
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
Oct 24, 2023
How would you reconstruct your formula in Column C? Curious.
Thanks for all your help!
George
Oct 24, 2023
Oct 24, 2023
Oct 24, 2023
Oct 24, 2023
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
Oct 24, 2023
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
Oct 24, 2023
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
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!
Oct 25, 2023
I appreciate the guidance on the posting protocol, too.
George
Oct 25, 2023
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
Oct 26, 2023
G
Oct 26, 2023
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
Oct 26, 2023
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
Oct 26, 2023
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
Oct 27, 2023
I'm now working on finding the recommendation post.
George
Oct 27, 2023
Please let me know if this is the correct way to recommend and also if you think I've indicated the best post.
George
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
Oct 27, 2023
George
Oct 27, 2023
Thank you,
Ben Liebrand