Question description:
This user has given permission to use the problem statement for this
blog.
I am attempting to create a function which generates a particular number value if a letter is found within a string of words. If that letter is found more than once than I want the numbers to be summed
Solved by D. A. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/09/2018 - 10:39
Welcome to ExcelChat!
Excelchat Expert
19/09/2018 - 10:39
I see that your question is about finding a letter and generating the result based on it
Excelchat Expert
19/09/2018 - 10:39
I will help you with the problem and explain the solution afterwards.
Excelchat Expert
19/09/2018 - 10:39
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
19/09/2018 - 10:39
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
19/09/2018 - 10:39
Sounds good
Excelchat Expert
19/09/2018 - 10:39
Could you provide some data sample or file based on which we need to make a solution?
User
19/09/2018 - 10:43
I am attempting to do that now.
Excelchat Expert
19/09/2018 - 10:43
I believe you can just type some text in A1, and explain which result do you want to get in B1 and why. In general I can quickly solve the problem that you described, but I need some more details
Excelchat Expert
19/09/2018 - 10:45
I would like to know which letter is required to be found, which result is expected to be returned. You also mentioned that if a letter was found more than once, then numbers have to be summed. I would like to understand which numbers do we need to sum in this case.
Excelchat Expert
19/09/2018 - 10:45
So just small sample of input and expected output and few explanations will be enough to cover all of these questions
User
19/09/2018 - 10:46
Let me send you a picture really quick to provide context
[Uploaded an Excel file]
Excelchat Expert
19/09/2018 - 10:47
Thank you, I copied the data from this sheet to the sheet near the chat. By the way, can you see the sheet near the chat?
User
19/09/2018 - 10:49
I can not. Here is a picture
User
19/09/2018 - 10:49
Were you able to get that?
[Uploaded an Excel file]
Excelchat Expert
19/09/2018 - 10:49
Yes, just appeared
Excelchat Expert
19/09/2018 - 10:50
Okay, I see the picture, but I don't understand it.. could you add some explanations?
Excelchat Expert
19/09/2018 - 10:51
You are trying to figure out how many times each letter is found in each of these boxes, right?
User
19/09/2018 - 10:51
Certainly Could. Do you see how the Y in blue is in the center column of the larger boxes and the center column of the letters within its box.
Excelchat Expert
19/09/2018 - 10:52
Yes, I can
User
19/09/2018 - 10:52
I was hoping that if a typed a word like yound I could get this to produce the number two in a column entitled middle column
User
19/09/2018 - 10:53
Moreover if there were two Ys I was hoping that it would produce the number 4
User
19/09/2018 - 10:53
*"young"
Excelchat Expert
19/09/2018 - 10:54
When we talk about the middle column, do we include both yellow and blue boxes?
User
19/09/2018 - 10:54
Yes we do
Excelchat Expert
19/09/2018 - 10:54
In this case for the word young, the result would be 3
Excelchat Expert
19/09/2018 - 10:54
Y U G
User
19/09/2018 - 10:55
My apologies I mean to say that the letter Y would produce the number two as it is nested within two middle columns. Does that make sense?
Excelchat Expert
19/09/2018 - 10:56
When you see the pop up, please extend the session
Excelchat Expert
19/09/2018 - 10:57
Well, I don't really understand it, since Y belongs only to one middle box, and can be found there only once..
User
19/09/2018 - 10:59
In the actual Program a participant is trying to type. Upon picking the blue box a similar configuration appears with the constituent letters of the blue box arranged as they are seen within the blue box. The participant must then select the center column a second time in order to pick the Y totalling two choices of the center column.
User
19/09/2018 - 11:01
I understand that this is a rather fuzzy description but I would be satisfied with knowing how out put a number for each occurence of a letter within a word generally
Excelchat Expert
19/09/2018 - 11:02
I can see in total 6 boxes with letters inside it. Inside these boxes, I can't see any other boxes.. do I have the right picture or I am missing something?
Excelchat Expert
19/09/2018 - 11:02
Okay, I will try to help you with more general question then which you asked
Excelchat Expert
19/09/2018 - 11:02
Because I am sure that we won't able to solve this quiz for the session time
User
19/09/2018 - 11:03
haha sounds good
Excelchat Expert
19/09/2018 - 11:04
Did I understand properly, that for example in the word apple, we need to know how many times we have the letter p inside it, and the result will be 2?
User
19/09/2018 - 11:06
I am looking to be able to assign a number to a letter rather than just count its occurences.
User
19/09/2018 - 11:07
For instance If a word contains Y I want that to count as 2
Excelchat Expert
19/09/2018 - 11:07
You can add some logic for that
Excelchat Expert
19/09/2018 - 11:07
For example, we count how many times we have Y and then multiply it by
Excelchat Expert
19/09/2018 - 11:07
by 2
User
19/09/2018 - 11:08
sounds Good
Excelchat Expert
19/09/2018 - 11:08
Okay, I will make for you an example for this case
Excelchat Expert
19/09/2018 - 11:09
I think you will understand how to use it and will able to create a complicated solution for your quiz
Excelchat Expert
19/09/2018 - 11:09
So we have word YOUNG in A1
Excelchat Expert
19/09/2018 - 11:09
In B1 we put the following formula
User
19/09/2018 - 11:10
Unfortunately the display is not showing up on my end
Excelchat Expert
19/09/2018 - 11:10
I will send the formula in a while to the chat
Excelchat Expert
19/09/2018 - 11:11
=(len(A1)-len(substitute(A1,"Y","")))*2
Excelchat Expert
19/09/2018 - 11:11
So how it works
Excelchat Expert
19/09/2018 - 11:11
1. We find the length of the word
Excelchat Expert
19/09/2018 - 11:11
2. We find the length of the word, if we replace each Y with nothing
Excelchat Expert
19/09/2018 - 11:11
3. We subtract the len of the word and the word without the required letter
Excelchat Expert
19/09/2018 - 11:12
4. We multiply the final result by 2
Excelchat Expert
19/09/2018 - 11:12
So for word YOUNG the final result is 2
User
19/09/2018 - 11:12
Awesome
User
19/09/2018 - 11:12
Thank you so much
Excelchat Expert
19/09/2018 - 11:12
If it is YOUNGY, the result is 4
Excelchat Expert
19/09/2018 - 11:12
I hope that will help you
Excelchat Expert
19/09/2018 - 11:12
Sorry that I didn't get that quiz..
User
19/09/2018 - 11:13
That totally fine. I'm sure if I was able to show you a video of someone using it two type it would make sense. Describing it was just difficult.
User
19/09/2018 - 11:14
THank you so much for your help!
Excelchat Expert
19/09/2018 - 11:14
Thanks for coming to Excelchat. You may now end the session and leave any comments or feedback. Have a nice day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
