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?

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?

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

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