Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy without altering the value | Excel Discussion (Misc queries) | |||
Altering text colours using a formula | Excel Discussion (Misc queries) | |||
Please help me with altering text in excel | New Users to Excel | |||
Altering the X-axis | Charts and Charting in Excel | |||
Formula keeps altering by itself! | Excel Worksheet Functions |