**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.*