Mining Numbers from text string.

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

I have spent some time looking threw existing posts and I can't seem
to find an exact answer to my problem.

If you can help....much thanks in advance.

It boils down to this....I want to do a vlookup for a text string,
instead of a "perfect match".

In other words I'd like to search for A1 (a number/text string) in
Column B:B, and provide data the data contained in columns B, C, and
D.

Thanks again,

Steve
 
Hi Steve

In a spare column you could use
=ISNUMBER(SEARCH($A$1,B2))
and copy down

Data>Filter>Autofilter>use dropdown on new column to select TRUE
 
Consider using AutoFilter rather than =VLOOKUP()

This way you can pick Custom and criteria like "contains", "begins with",
"ends with", etc.

Only the rows matching your criteria will be displayed.
 
Thanks Roger...you've got me much closer but this isn't quite what I
need. I am getting both incorrect trues and falses.

I have approximately 2000 serial numbers, some of which have alpha
characters. I need to know if any of these SN's are in approx 11k
lines of data that will have various texts strings.

For example. Let's say I have a column of 2000 SN's, the first one is
101M. I need to know if it is somewhere in a column of 11,000 cells/
rows and it could be in a cell such as delkcpm101m or pu kcpm 101m.
 
Will there be a *single* match, or the possibility of *numerous* matches?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Thanks Roger...you've got me much closer but this isn't quite what I
need. I am getting both incorrect trues and falses.

I have approximately 2000 serial numbers, some of which have alpha
characters. I need to know if any of these SN's are in approx 11k
lines of data that will have various texts strings.

For example. Let's say I have a column of 2000 SN's, the first one is
101M. I need to know if it is somewhere in a column of 11,000 cells/
rows and it could be in a cell such as delkcpm101m or pu kcpm 101m.
 
Hello RD thanks for "jumping in",

There will possibly be multiple matches (duplicate serial numbers) but
not many and I would gladly look these up manually. I could make them
unique but that too is a long manual process.

I'm getting frustrated...a thought just "hit me", could I do a macro/
routine that would automate the Edit/Find function?

Many thanks,

Steve
 
Let me rephrase my question:

You say that once you find the serial number that contains a particular
string, you will want the data returned from Columns B, C, and D of that
row.

My question is, whether or not there are exact duplicate serial numbers
containing that particular string, or ... other, different serial numbers
with that string included, will the data in the adjoining Columns B, C, and
D be *different* for each of these occurrences (matches), where you will
want numerous different rows of data returned?

In other words, Vlookup() will return *only* the first occurrence of a
match.
This will give you *one* set of returns from the referenced columns in the
"found" row,

If the duplicate serial numbers in that first column have the identical data
in those referenced columns (B, C, D), then you *don't* need multiple
returns.

Follow?

Say you're looking for a serial number containing the string:
101M
Which you enter in say, G1.

Say you datalist is in A2 to D2000, with the serial numbers listed down
Column A.

This will find the *first* occurrence of a s/n containing that string match,
and return the data from the 2nd column (B):

=VLOOKUP("*"&G1&"*",A2:D2000,2,0)

You could revise this so that you could copy it across columns, along a row,
to return the data in the 3rd and 4th columns (C and D) of the datalist:

=VLOOKUP("*"&$G$1&"*",$A$2:$D$2000,COLUMNS($A:B),0)

*BUT* ... this will give you only *one set* of data.

Is this good enough?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hello RD thanks for "jumping in",

There will possibly be multiple matches (duplicate serial numbers) but
not many and I would gladly look these up manually. I could make them
unique but that too is a long manual process.

I'm getting frustrated...a thought just "hit me", could I do a macro/
routine that would automate the Edit/Find function?

Many thanks,

Steve
 
RD,

In regards to your comments early in your latest reply...I did want
more initially but began scaling back my "needs vs wants" when it
didn't seem likely....however....

Looks like you may have the answer for me. I didn't know you could
"make" vlookup search for text strings...excellent!!

I will check it out. I am not used to using the --- ("*"&G1&"*", --

Sincere thanks for your assistance.

Steve
 
You're welcome ... and post back if your "needs" and/or "wants" exceed the
capabilities of that Vlookup formula.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

RD,

In regards to your comments early in your latest reply...I did want
more initially but began scaling back my "needs vs wants" when it
didn't seem likely....however....

Looks like you may have the answer for me. I didn't know you could
"make" vlookup search for text strings...excellent!!

I will check it out. I am not used to using the --- ("*"&G1&"*", --

Sincere thanks for your assistance.

Steve
 
RD,

That was the solution to my problem!

Sincerely appreciate you and all the others who volunteer to assist
others on this and other groups.


Steve
 
Back
Top