Lookup Value divided by hyphen?

[XLML]

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

 

[nbrcrunch]

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!

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]

How about this ?


=RIGHT(A1,LEN(A1)-(FIND("-",A1)))

 

[vane0326]

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
	A	B	C	D	E
1	100	89257-31		31	100
2				1	8
3				8	56
4				29	70
5
Sheet1

2nd Example:

Right side vaule of the hyphen.xls
	A	B	C	D	E
1	70	85-29		31	100
2				1	8
3				8	56
4				29	70
5
Sheet1

 

[Krishnakumar]

Hi,

Book1
	A	B	C	D	E
1	8	21-2112-132		31	100
2				132	8
3				8	56
4				29	70
Sheet1

Formula in A1,

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

HTH

 

[nbrcrunch]

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]

Is this it?

Book1
	A	B	C	D
1	xx-aa	22	1	a
2	x-9-xx	44	aa	22
3	uu--88	string 88	bb	33
4			xx	44
5			88	numeric 88
6			88	string 88
Sheet1

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

 

[XLML]

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]

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]

Re: 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

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 }