Extracting a number from a text string and returning a third value

[steelervince]

Hello All!

I have one Excel file

In Column B (275 rows) I have a number (all of these numbers are unique)
In Column C (275 rows) I have a string of text which contains several numbers within the string (all of the text strings are unique)
In Column D (275 rows) I have a different number from Column B (all of these numbers are unique)

what I need:

a formula in Column E that
Looks at the number in column B
Searches the text string in Column C for presence of the number in Column B
If the number from Column B exists in the text string in Column C then, return the number from Column D otherwise return the text "N/A"

Any help would be appreciated.

V

 

[James006]

Hello,

Feel free to post a couple of examples to illustrate your expected result ...

 

[Joe4]

Without seeing any data, this is what I think you want (place formula in E2 and copy down):

=IF(ISNUMBER(SEARCH(B2,C2)),D2,"N/A")

 

[steelervince]

Column B
123456

Column C
186546 - some text; 134532 - some more text; 123456 - even more text

Column D
435671

Column E
I want excel to return 435671 here (in this example otherwise N/A goes here)

 

[Joe4]

Did you try my formula?

 

[steelervince]

Yes, I did Joe and it returned N/A in every cell in Column E

 

[Joe4]

Yes, I did Joe and it returned N/A in every cell in Column E

It works for me on the example you presented.

I am guessing that either:
1. You didn't update the formula correctly (to reflect the right row)
- or -
2. The are "extra" characters in column B that you aren't accounting for

Make sure that you do not have any extra/special/invisible characters in column B.

So, in your example, if "123456" is in cell B2, then see what this formula returns:
=LEN(B2)
If it returns any number greater than 6, you have some extra characters in there that you need to clean up (or adjust the formula accordingly).

 

[steelervince]

Thank you Joe!

This one is on me!!!! I forgot to mention that for each value in column B, I need the formula to search in over 1500 rows of data in Column C. So, I have B2 value as 123345 and then there are over 1500 rows of data in column C with varying text strings. If the value in B2 matches a value in say C150 then, I want the corresponding D150 cell number returned in E2.

I am sorry about that. Thank you for your time!

V

 

[Joe4]

OK, I see. Yes, very different.

Try this (in cell E2):

=VLOOKUP("*"&B2&"*",C:D,2,0)

 

[steelervince]

Joe

Excel returns a Value Not Available error and #N/A in column E. I understand the use of the * however, is Excel making the value in column B (for example) *123456*? I believe that what you are attempting to do is to have the value in column B have the wildcard characters before and after it, correct?