Lookup Value divided by hyphen?

XLML

Active Member
Joined
Aug 15, 2003
Messages
407
The lookup value that I would like to reference in a vlookup formula is after the last hyphen in cell A1. There is a variable number of hyphens and characters in cell A1.


How can I extract the value after the last hyphen "-" to use in a vlookup formula?

Thanks in advance,
XLML
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Oh boy this was fun. I actually coded this from scratch with absolutely no help from any of my myriads of references. Hey, that'll be $100 mister! :LOL:

Put this in a new module:
Function ExtractMe(mycell As Range)

For x = 1 To Len(mycell)
If Mid(mycell, x, 1) = "-" Then
ExtractMe = x + 1

End If
Next x

End Function


Then, use this formula in any cell.
=MID(A1,extractme(A1),LEN(A1))
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
XLML said:
The lookup value that I would like to reference in a vlookup formula is after the last hyphen in cell A1. There is a variable number of hyphens and characters in cell A1.


How can I extract the value after the last hyphen "-" to use in a vlookup formula?

Thanks in advance,
XLML



After reading your post again is this what you are looking for ?


=VLOOKUP(--RIGHT(B1,LEN(B1)-(FIND("-",B1))),D1:E4,2,0)

1st Example:
Right side vaule of the hyphen.xls
ABCDE
110089257-3131100
218
3856
42970
5
Sheet1



2nd Example:
Right side vaule of the hyphen.xls
ABCDE
17085-2931100
218
3856
42970
5
Sheet1
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615

ADVERTISEMENT

Hi,
Book1
ABCDE
1821-2112-13231100
21328
3856
42970
Sheet1


Formula in A1,

=VLOOKUP(--REPLACE(B1,1,LOOKUP(9.9999E+307,FIND("-",B1,ROW($1:$1024))),""),D1:E4,2,0)

HTH
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Well, after reading Vane & Krish's posts, now I'm confused. From the way I read it, the serialized number that needed parsing was in cell A1. It would always be in cell A1.

xlml's need was to lookup and/or perform math operations on the numeric value following the last dash. The last sentence in the post is: "How can I extract the value after the last hyphen "-" to use in a vlookup formula? "

My solution provided that value. I'm not following what the results are for other two respondents. (But then, I've been known to be "dense" before.) ;)
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Is this it?
Book1
ABCD
1xx-aa221a
2x-9-xx44aa22
3uu--88string 88bb33
4xx44
588numeric 88
688string 88
Sheet1


Notice that, as is, it will not find a numeric target.
 

XLML

Active Member
Joined
Aug 15, 2003
Messages
407
Thanks for all the replies

I got the following to work: just_jon & vane0326 (for only 1 hyphen)

Nbrcrunch - I didn't get yours to work but that is probably because I am not the best at UDF.

Krish - I couldn't get it to work but did not know how to modify.

I can't get any response to work in the following macro

Dim i As Long, f As String, fn As String
R1C1 = InputBox("Choose Cell", "Enter Value")
f = InputBox("Enter Formula", "Enter Value")
If R1C1 = "" Then Exit Sub
If f = "" Then Exit Sub

It errors out on the following line:
Range(R1C1) = f

Any ideas?
XLML
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
What value(s) is actually being entered?

By the way is this actually related to the original topic.

If it isn't it might be an idea to start a new thread.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Re: Thanks for all the replies

XLML said:
I got the following to work: just_jon & vane0326 (for only 1 hyphen)

Nbrcrunch - I didn't get yours to work but that is probably because I am not the best at UDF.

Krish - I couldn't get it to work but did not know how to modify.

I can't get any response to work in the following macro

Dim i As Long, f As String, fn As String
R1C1 = InputBox("Choose Cell", "Enter Value")
f = InputBox("Enter Formula", "Enter Value")
If R1C1 = "" Then Exit Sub
If f = "" Then Exit Sub

It errors out on the following line:
Range(R1C1) = f

Any ideas?
XLML

Should work for any number of hyphens ( see the 2nd row of my posted sample above )

In B2: =VLOOKUP(RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,"-","~",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))),C:D,2,0)

Returns: xx { as the target, ie 1st argument in VLOOKUP }
 

Watch MrExcel Video

Forum statistics

Threads
1,118,822
Messages
5,574,520
Members
412,600
Latest member
Andyb2
Top