How do I create a formula using part of the data in 1 cell?

S

SMC0890

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?
 
B

Bearacade

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
 
C

Casey

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)
INSERT>NAME>DEFINE

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
 
Top