PDA

View Full Version : [SOLVED] Comparing part of a Cell volume against a range



hcadar1
08-15-2005, 06:02 AM
I need to extract a value of a cell up to a space or hypen in the field for example the cell value may be 301-555-1212 or 43 4958 4848. I need to extact the 301 or the 43 and output for a vlookup we do. I tried searching the boards for some time and cant find how to do it. Any suggestions oh great ones : pray2:

Killian
08-15-2005, 06:21 AM
You can use the Split function (you'll have to write your own if you have XL97) after testing for a hyphen or space. It will provide you with an array, so use the first index for your lookup value.


'For the sake of an example, I'm testing the active cell
Sub GetPrefix()
Dim rngTarget As Range
Dim arrNumber
Dim strSlpitChar As String
Set rngTarget = ActiveCell
If InStr(1, rngTarget.Text, "-") <> 0 Then
strSlpitChar = "-"
ElseIf InStr(1, rngTarget.Text, " ") <> 0 Then
strSlpitChar = " "
End If
If strSlpitChar <> "" Then
arrNumber = Split(rngTarget.Text, strSlpitChar)
MsgBox "Prefix is " & arrNumber(0)
End If
End Sub

hobgoblin
08-15-2005, 08:19 AM
That's a formula that i'm using to extract text left of "-" or the " ". it may be not perfect, but it's a start. Also, take note that im using the french version of Excel, so you will probably have to find the corresponding english formulas and in some areas to replace the ; by , :

So here it goes. If your original text is in cell a1:


=STXT(A1;1;SI(SI(ESTERREUR(CHERCHE("-";A1));NBCAR(A1);CHERCHE("-";A1)-1)<SI(ESTERREUR(CHERCHE(" ";A1));NBCAR(A1);CHERCHE(" ";A1)-1);SI(ESTERREUR(CHERCHE("-";A1));NBCAR(A1);CHERCHE("-";A1)-1);SI(ESTERREUR(CHERCHE(" ";A1));NBCAR(A1);CHERCHE(" ";A1)-1)))

hobgoblin
08-15-2005, 08:25 AM
This is the english version although not tested :

=MID(A1,1,If(If(IsError(Find("-",A1)),LEN(A1),FIND("-",A1)-1)<If(IsError(Find(" ",A1)),Len(A1),Find(" ",A1)-1),If(IsError(Find("-",A1)),Len(A1),Find("-",A1)-1),If(IsError(Find(" ",A1)),Len(A1),Find(" ",A1)-1)))

Bob Phillips
08-15-2005, 08:47 AM
I need to extract a value of a cell up to a space or hypen in the field for example the cell value may be 301-555-1212 or 43 4958 4848. I need to extact the 301 or the 43 and output for a vlookup we do. I tried searching the boards for some time and cant find how to do it. Any suggestions oh great ones : pray2:

Here is a formula to do it all in one swoop


=VLOOKUP(LEFT(A1,MIN(IF(NOT(ISERROR(1*(MID(A1,row_array,1)))),255,row_array ))-1),M1:P100,2,FALSE)

Two things

1. This is an array formula, so commit with Ctrl-Shift-Enter

2. row-array is an Excel name which refers to a this formula in this formula

=ROW(INDIRECT("A1:A"&LEN(Sheet2!A1)))
I have had to do it this way as the formula has more than 7 nested functions otherwise.

hcadar1
08-15-2005, 10:25 AM
Thanks. That works

hcadar1
08-15-2005, 01:22 PM
Ok... I tried to incorporate both xld's and hobogoblins response into a macro I have written but getting a Type Mismatch error.

Things are just dandy when entered as a formula in the ss but when i change it to


ActiveCell.FormulaR1C1 = "=MID(RC[-14],1,IF(IF(ISERROR(FIND(""-"",RC[-14])),LEN(RC[-14]),FIND(""-"",RC[-14])-1)<IF(ISERROR(FIND("" "",RC[-14])),LEN(RC[-14]),FIND("" "",RC[-14])-1),IF(ISERROR(FIND(""-"",RC[-14])),LEN(RC[-14]),FIND(""-"",RC[-14])-1),IF(ISERROR(FIND("" "",RC[-14])),LEN(RC[-14]),FIND("" "",RC[-14])-1)))"

Any ideas??

Bob Phillips
08-15-2005, 03:47 PM
I just tried that formula, and it worked fine for me.

hcadar1
08-16-2005, 05:06 AM
Found the issue. Had to put an error trap on there since not every cell would have a value. thanks for everyones help