altering text to other column
 

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

altering text to other column
 

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")

In article ,
Terry Berry <Terry wrote:

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

altering text to other column
 

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.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Terry Berry" wrote:

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

altering text to other column
 

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
---
"Terry Berry" wrote:
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

altering text to other column
 

Hi,

You can download a list of the states and their abbreviations from

http://www.usps.com/ncsc/lookups/usp...eviations.html

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Terry Berry" wrote:

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