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
 
Sorry for the confusion just_jon. Vane's only works for 1 hypen. Your formula works.

Norie,
I can repost if necessary.

XLML
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
It's not really necessary but it's a good idea to make sure threads don't wander off topic too much.
 
Upvote 0
I know this horse (thread) has already been beat to death, but it dawned on me today that I really could have had the UDF complete the whole routine and merely return the needed value. I've enhanced it to include that functionality so that all the user needs to do is enter the formula:

=ExtractMe(CellReference)
where CellReference is the cell containing the text string

Here's the updated UDF:

Function ExtractMe(mycell As Range)

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

End If
Next x

ExtractMe = Mid(mycell, MyPosition + 1, Len(mycell)) * 1

End Function
 
Upvote 0
Hi,
Book1
ABCD
1xx-aa221a
2x-9-xx44aa22
3uu--88numeric 88bb33
4xx44
588numeric 88
688string 88
Sheet1


Formula in B1,

=IF(ISNA(VLOOKUP(REPLACE(A1,1,LOOKUP(9.9999E+307,FIND("-",A1,ROW($1:$1024))),""),$C$1:$D$6,2,0)),VLOOKUP(--REPLACE(A1,1,LOOKUP(9.9999E+307,FIND("-",A1,ROW($1:$1024))),""),$C$1:$D$6,2,0),VLOOKUP(REPLACE(A1,1,LOOKUP(9.9999E+307,FIND("-",A1,ROW($1:$1024))),""),$C$1:$D$6,2,0))

HTH
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top