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

 

[Joe4]

I forgot about the "N/A" part.

Try this variation:

=IFERROR(VLOOKUP("*"&B2&"*",C:D,2,0),"N/A")

 

[steelervince]

Joe

Every single cell in column E now has N/A in it. I wish that I could post an image of my cells so that you can see how much data is in column C. Columns B, C and D are all General format

 

[Joe4]

Every single cell in column E now has N/A in it. I wish that I could post an image of my cells so that you can see how much data is in column C. Columns B, C and D are all General format

You can post images, using the tools mentioned here in section B: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. But please do not attempt to post some huge, monstrous image! If you want to post a small sample, that is fine.

Just how much data are we talking about here? Perhaps Excel is not the best tool to use, if the data is getting too large, and something like Access would be better.

Here is "proof of concept" that it should work in Excel. This will populate sample data in cells B2:D5, and then populate column E with formulas.
The first two will have matches are return values, and the last two will not have matches.
Copy this VBA code over, and run on any blank sheet and see what happens:

Sub TestMacro()
'   Populate column B with data
    Range("B2") = 123456
    Range("B3") = 123457
    Range("B4") = 999999
    Range("B5") = 888888
'   Populate column C with data
    Range("C2") = "186546 - some text; 134532 - some more text; 123456 - even more text"
    Range("C3") = "186546 - some text; 134532 - some more text; 123456 - even more text"
    Range("C4") = "186546 - some text; 134532 - some more text; 12578 - even more text"
    Range("C5") = "186546 - some text; 134532 - some more text; 123457 - even more text"
'   Populate column D with data
    Range("D2") = 435671
    Range("D3") = 435672
    Range("D4") = 435673
    Range("D5") = 435674
'   Populate column E with formulas
    Range("E2:E5").FormulaR1C1 = _
        "=IFERROR(VLOOKUP(""*""&RC[-3]&""*"",C[-2]:C[-1],2,0),""N/A"")"
End Sub

I think you will find that it works.
If yours does not work, compare these two scenarios, and see what is different.

Also make sure that your values in column B do not have any extra/imvisible characters, messing things up.
You can use the length (LEN) function to confirm that.

 

[sandy666]

maybe try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

123456​

[/td][td=bgcolor:#DDEBF7]186546 - some text; 134532 - some more text; 123456 - even more text[/td][td=bgcolor:#DDEBF7]

435671​

[/td][td=bgcolor:#E2EFDA]435671[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

223344​

[/td][td]186547 - some text; 134532 - some more text; 123457 - even more text[/td][td]

345678​

[/td][td]NA[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    Condition = Table.AddColumn(Type, "Custom", each if Text.Contains([Column2], [Column1]) then [Column3] else "NA"),
    ROC = Table.SelectColumns(Condition,{"Custom"})
in
    ROC[/SIZE]

 

[Joe4]

Sandy,

I cannot tell from the simplified example you posted, but will that solution check every row in column C? It looks like it might just be checking the same row (which was the initial mistake I made).
So, they want B2 to search every row in column C, and if it finds a match, return the value from D in that matching row.
For example, if the value in B2 is found within cell C100, they want to return the value of D100 in cell E2.

 

[sandy666]

I assumed if c1 contain value from b1 then d1 else NA
but not if c1 contain value from b100 ... etc
OP didn't say that or I misunderstood

 

[steelervince]

Joe

Your code worked on a blank spreadsheet. Thank you!


Then, I copied and pasted all of my data into a brand new workbook devoid of any formatting and ensured that the character length in all cells in column B were exactly 6. I deleted all blank rows and cells. I created the formula and again I received the N/A.

My company has our LTs locked down pretty tight and I am unable to download the add-in needed to upload the image of my data.

V

 

[steelervince]

Yes

 

[steelervince]

Sandy,

I cannot tell from the simplified example you posted, but will that solution check every row in column C? It looks like it might just be checking the same row (which was the initial mistake I made).
So, they want B2 to search every row in column C, and if it finds a match, return the value from D in that matching row.
For example, if the value in B2 is found within cell C100, they want to return the value of D100 in cell E2.

That is correct Sandy

 

[steelervince]

I took the following steps to see where my VLOOKUP was producing an ERROR:

Copied my data in Column B and pasted it in a brand new workbook as text
Copied all of the data that was in Column C and pasted it in the new workbook as text
Copied all of the data that was in Column D and pasted it in the new workbook as text

In Column E of the new workbook, I created the formula:
=IFERROR(VLOOKUP("*"&B2&"*",C:D,2,0),"N/A")

and in Column E for each row "N/A" was the result.

I then copied the values that were in Column B and pasted them into Column C to see if the formula would work and it did NOT.
I then changed the formula to this:
=IFERROR(VLOOKUP(B2,C:D,2,0),"N/A") and the formula produced the correct results in Column E.

I am thinking that the ERROR is happening with the "*"&B2&"*" portion of the formula.

Any ideas?