# Lookup Value divided by hyphen?

#### XLML

##### Active Member
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?

XLML

### 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
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

##### Well-known Member

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

#### vane0326

##### Well-known Member
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?

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

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

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
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
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
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 }

Replies
8
Views
98
Replies
5
Views
81
Replies
2
Views
154
Replies
5
Views
55
Replies
0
Views
162