| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup partial text from a single cell
I use a spread sheet which includes the customer address (city and state) in
a single (merged) cell. The spreadsheet also containes a look up table with 2-letter state abbreveations. I need to capture only the state from the address (cell E5) for the vlookup. =IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)=0,"NO",IF(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup partial text from a single cell
Are you saying the you want to use "California" in the address cell to return
"CA"? How are the City/State delimited in the address cell? "Sparky13" wrote: I use a spread sheet which includes the customer address (city and state) in a single (merged) cell. The spreadsheet also containes a look up table with 2-letter state abbreveations. I need to capture only the state from the address (cell E5) for the vlookup. =IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)=0,"NO",IF(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup partial text from a single cell
If the state code is the last two letters in cell E5:
VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE) Sparky13 wrote: I use a spread sheet which includes the customer address (city and state) in a single (merged) cell. The spreadsheet also containes a look up table with 2-letter state abbreveations. I need to capture only the state from the address (cell E5) for the vlookup. =IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)=0,"NO",IF(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup partial text from a single cell
Thank you Deb, this does exactly what I wanted.
"Debra Dalgleish" wrote: If the state code is the last two letters in cell E5: VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE) Sparky13 wrote: I use a spread sheet which includes the customer address (city and state) in a single (merged) cell. The spreadsheet also containes a look up table with 2-letter state abbreveations. I need to capture only the state from the address (cell E5) for the vlookup. =IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)=0,"NO",IF(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup partial text from a single cell
You're welcome. Thanks for letting me know that it helped.
Sparky13 wrote: Thank you Deb, this does exactly what I wanted. "Debra Dalgleish" wrote: If the state code is the last two letters in cell E5: VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE) Sparky13 wrote: I use a spread sheet which includes the customer address (city and state) in a single (merged) cell. The spreadsheet also containes a look up table with 2-letter state abbreveations. I need to capture only the state from the address (cell E5) for the vlookup. =IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)=0,"NO",IF( VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to diffrentiate Text & number from a single cell???? | New Users to Excel | |||
| Cell Capacity - text | Excel Discussion (Misc queries) | |||
| Formula to transfer text from merged cells to single cell? | Excel Discussion (Misc queries) | |||
| Finding Partial Text in a Cell | Excel Worksheet Functions | |||
| Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) | |||
Hybrid Mode
