altering text to other column

  • Thread starter Thread starter Terry Berry
  • Start date Start date
T

Terry Berry

I have a database that includes states and district numbers in separate
fields. I need to take those 2 fields and change them to a third combined
listing.
For Example:

Tennessee 5 and change to TN_05
Arkansas 2 and change to AR_02

I will eventually have over 3000 entries and any help would be greatly
appreciated
 
Assuming you're using a lookup table for the abbreviations (say, in
Sheet2, columns J:K):


=VLOOKUP(A1,Sheet2!J:K,2,FALSE) & TEXT(B1,"\_00")
 
You will also need a list of the State Name and the State abreviation to be
able to return just the two letters.
If you alredy have it you could use a vlookup function to return the
abreviation combined with the concatenate function something like this:

=Concatenate(vlookup(A1,$B$1:$C$52,2,False),"_",D1)
This assumes that the list I mentioned Above is in columns B and C, rows 1
to 52; it also assumes your state is in column A and that the district is in
column D; the formula itself is in column E.
something like this:
A B C D E
Texas Texas Tx 5 Formula above

This would return Tx_5
Your list of State/Abreviation is probably somewhere else, therefore, change
the cell refrence in the formula to suit your needs.
 
Assuming you have all the abbrev. definitions
in Sheet2's cols A and B, ie:

Tennessee TN
Arkansas AR
etc

and in Sheet1,
you have/will be entering in A2:B2 down

Tennessee 5
Arkansas 2
etc
(nums in B2 down are assumed real numbers)

then you could place in C2:
=IF(COUNTA(A2:B2)<2,"",VLOOKUP(A2,Sheet2!A:B,2,0)&"_"&TEXT(B2,"00"))
and just copy C2 down all the way
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
 
Back
Top