Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a formula using part of the data in 1 cell?
I am trying to create a conditional formula that will look at the first three
characters in a cell and determine a value based on that. Here are four examples of the data I am looking at and the values I need returned for each: Data Value returned should be TAD12345 Ab TED1263 Ed TAC25462 Ab CI TAT9456 Ab TRM I need a conditional formula that will look at the first three letters in each of these and return the value based on that. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a formula using part of the data in 1 cell?
=IF(LEFT(A1,3)="TAD","Ab","")
Replace "" with other IF statements as appropriate. "SMC0890" wrote: I am trying to create a conditional formula that will look at the first three characters in a cell and determine a value based on that. Here are four examples of the data I am looking at and the values I need returned for each: Data Value returned should be TAD12345 Ab TED1263 Ed TAC25462 Ab CI TAT9456 Ab TRM I need a conditional formula that will look at the first three letters in each of these and return the value based on that. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a formula using part of the data in 1 cell?
You would need to set up a table and then use vlookup and left function. Let's say your data is in column A set up a table in column F:G F as your lookup value and G as your return value: F G tad Ab ted Ed tac Ab CI tat Ab TRM and in B put this in: =VLOOKUP(LEFT(A1,3),F1:G4,2) HTH -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=570896 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a formula using part of the data in 1 cell?
Hi, I would first create a table of two columns and as many rows as you need. With your data mine looks like this. TAD Ab TED Ed TAC Ab CI TAT Ab TRM Then I name the table "YourTable" (you can call it what you want to) INSERTNAMEDEFINE Then the following formula works for me, Assuming your data starts in cell A1 (change to suit) =VLOOKUP(LEFT(A1,3),YourTable,2,FALSE) HTH -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=570896 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Any cell containing formula seen as data instead of formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |