Question description:
This user has given permission to use the problem statement for this
blog.
C5 contains 1625MPGW, F5 contains quantity. Need to search for MPGW and have quantity insert into column H5.
Solved by E. C. in 33 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/03/2018 - 04:49
Welcome to excel got it pro.
Excelchat Expert
05/03/2018 - 04:49
I'm here to help you today.
Excelchat Expert
05/03/2018 - 04:50
Would you please show me some sample data?
Excelchat Expert
05/03/2018 - 04:51
Thanks.
Excelchat Expert
05/03/2018 - 04:52
Is there anything else?
User
05/03/2018 - 04:54
I would like a function that will search A3 for MPGW and put the quantity of B3 in C3.
Excelchat Expert
05/03/2018 - 04:55
You mean search column A and put the qty of column B?
User
05/03/2018 - 04:56
MPGW = White MPG=Yellow
Excelchat Expert
05/03/2018 - 04:57
I'm still not sure what you want.
User
05/03/2018 - 04:57
One second.
User
05/03/2018 - 04:59
A3 contains the letters "MPGW" if MPGW is present, insert B3 value into C3.
Excelchat Expert
05/03/2018 - 04:59
is that it?
User
05/03/2018 - 05:00
A4 contains the letters "MPG" if "MPG" is present, insert B4 value into D4.
Excelchat Expert
05/03/2018 - 05:01
let me see what I can do for you. I will need a couple of minutes.
User
05/03/2018 - 05:02
The problem i was having is when i would drag the function down it was picking up the "MPG" for "MPGW" because technically it contained "MPG"
Excelchat Expert
05/03/2018 - 05:03
Yeah, that might happen.
Excelchat Expert
05/03/2018 - 05:05
Is it a problem if I use a helper column?
Excelchat Expert
05/03/2018 - 05:06
I will need more time, so please select yes when you are asked.
Excelchat Expert
05/03/2018 - 05:08
I'm waiting for your confirmation.
User
05/03/2018 - 05:09
I Clicked yes
Excelchat Expert
05/03/2018 - 05:09
Ah yes and if I use a helper column.
Excelchat Expert
05/03/2018 - 05:09
Is that a problem?
User
05/03/2018 - 05:10
sure
User
05/03/2018 - 05:10
i will show you what i have, one second
User
05/03/2018 - 05:11
=IF(ISNUMBER(SEARCH("MPGW",A3)),VALUE(B3),"")
User
05/03/2018 - 05:12
enter in c3 and drag down.
Excelchat Expert
05/03/2018 - 05:12
I see.
User
05/03/2018 - 05:12
it works
User
05/03/2018 - 05:12
but.......
User
05/03/2018 - 05:12
=IF(ISNUMBER(SEARCH("MPG",A3)),VALUE(B3),"")
User
05/03/2018 - 05:12
enter in D3 and it picks up the MPGW
Excelchat Expert
05/03/2018 - 05:12
Yup, it will.
Excelchat Expert
05/03/2018 - 05:13
I have created a new column at column F. It will extract the non-numeric values.
Excelchat Expert
05/03/2018 - 05:13
Then we can use this column.
User
05/03/2018 - 05:13
Understood
Excelchat Expert
05/03/2018 - 05:14
Now, you will not have the previous issue.
Excelchat Expert
05/03/2018 - 05:16
now the helper column no longer needed.
Excelchat Expert
05/03/2018 - 05:17
any question?
User
05/03/2018 - 05:18
Hmm I will have to try it out on my end.
Excelchat Expert
05/03/2018 - 05:18
sure, take your time.
User
05/03/2018 - 05:20
what did you do to create the helper column
User
05/03/2018 - 05:20
as in how did you take the numbers out
Excelchat Expert
05/03/2018 - 05:20
The helper column does't need any more.
Excelchat Expert
05/03/2018 - 05:21
MID(A3,5,LEN(A3)-4) part takes the numbers out.
User
05/03/2018 - 05:21
I got it
User
05/03/2018 - 05:21
It worked!!
Excelchat Expert
05/03/2018 - 05:22
Okay.
User
05/03/2018 - 05:22
Thank you!
Excelchat Expert
05/03/2018 - 05:22
My pleasure.
Excelchat Expert
05/03/2018 - 05:22
A five star rating would be much appreciated.
User
05/03/2018 - 05:22
I will do that !
Excelchat Expert
05/03/2018 - 05:22
To rate me click on the end session button.
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.