Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformatting
I received an Excel file with data I need to reformat and do not know how.
1. Is there a way I can write a formula to change data from 1/13/1996 to 1131996 [no slashes]? 2. I have telephone numbers 760-123-4567 I need changed to 7601234567 [no dashes] 3. One column has CA - California. All I need is CA How do I truncate this? I have approximately 400 records and would appreciate anybody's help. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformatting
hi
1. =MONTH(D3)&DAY(D3)&YEAR(D3) 2. =SUBSTITUTE(F3,"-","") 3. =LEFT(D5,2) adjust cell reference to suit your data; regards FSt1 "ILoveMyCorgi" wrote: I received an Excel file with data I need to reformat and do not know how. 1. Is there a way I can write a formula to change data from 1/13/1996 to 1131996 [no slashes]? 2. I have telephone numbers 760-123-4567 I need changed to 7601234567 [no dashes] 3. One column has CA - California. All I need is CA How do I truncate this? I have approximately 400 records and would appreciate anybody's help. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformatting
Thank you so much! I hope you have a great evening.
Susan "FSt1" wrote: hi 1. =MONTH(D3)&DAY(D3)&YEAR(D3) 2. =SUBSTITUTE(F3,"-","") 3. =LEFT(D5,2) adjust cell reference to suit your data; regards FSt1 "ILoveMyCorgi" wrote: I received an Excel file with data I need to reformat and do not know how. 1. Is there a way I can write a formula to change data from 1/13/1996 to 1131996 [no slashes]? 2. I have telephone numbers 760-123-4567 I need changed to 7601234567 [no dashes] 3. One column has CA - California. All I need is CA How do I truncate this? I have approximately 400 records and would appreciate anybody's help. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformatting
From your response to FSt1 it seems the formula =LEFT(D5,2)
will work for California. If you have other states in that column then it won't work. For example all the New... states and all the North... states and all the A... states. Just curious if it was a sound solution. Otherwise you may need to do a two column list of the states in one and their two letter designation in another and use Vlookup to get the state abbreviation. HTH Regards, Howard "ILoveMyCorgi" wrote in message ... I received an Excel file with data I need to reformat and do not know how. 1. Is there a way I can write a formula to change data from 1/13/1996 to 1131996 [no slashes]? 2. I have telephone numbers 760-123-4567 I need changed to 7601234567 [no dashes] 3. One column has CA - California. All I need is CA How do I truncate this? I have approximately 400 records and would appreciate anybody's help. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformatting
hi
i assumed that the abrevation was us postal. ALL us postal abrevations are 2 digit. see this site.... http://www.usps.com/ncsc/lookups/usp...eviations.html if as you suspect and the source is using a non-us postal system then the op has a problem we can't solve. sigh maybe a polite email to the source might improve the data exchange in the future. regards FSt1 "L. Howard Kittle" wrote: From your response to FSt1 it seems the formula =LEFT(D5,2) will work for California. If you have other states in that column then it won't work. For example all the New... states and all the North... states and all the A... states. Just curious if it was a sound solution. Otherwise you may need to do a two column list of the states in one and their two letter designation in another and use Vlookup to get the state abbreviation. HTH Regards, Howard "ILoveMyCorgi" wrote in message ... I received an Excel file with data I need to reformat and do not know how. 1. Is there a way I can write a formula to change data from 1/13/1996 to 1131996 [no slashes]? 2. I have telephone numbers 760-123-4567 I need changed to 7601234567 [no dashes] 3. One column has CA - California. All I need is CA How do I truncate this? I have approximately 400 records and would appreciate anybody's help. Thank you. . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformatting
If they were all just two letter abbreviations then just the cell reference
would suffice, =(D5). I read it as the state is spelled out and in that case Alaska & Alabama return the same, as do NOrth Dakota & NOrth Carolina and all the NEw... Jersey, York, Hampshire, Mexico states and NEvada. Regards, Howard "FSt1" wrote in message ... hi i assumed that the abrevation was us postal. ALL us postal abrevations are 2 digit. see this site.... http://www.usps.com/ncsc/lookups/usp...eviations.html if as you suspect and the source is using a non-us postal system then the op has a problem we can't solve. sigh maybe a polite email to the source might improve the data exchange in the future. regards FSt1 "L. Howard Kittle" wrote: From your response to FSt1 it seems the formula =LEFT(D5,2) will work for California. If you have other states in that column then it won't work. For example all the New... states and all the North... states and all the A... states. Just curious if it was a sound solution. Otherwise you may need to do a two column list of the states in one and their two letter designation in another and use Vlookup to get the state abbreviation. HTH Regards, Howard "ILoveMyCorgi" wrote in message ... I received an Excel file with data I need to reformat and do not know how. 1. Is there a way I can write a formula to change data from 1/13/1996 to 1131996 [no slashes]? 2. I have telephone numbers 760-123-4567 I need changed to 7601234567 [no dashes] 3. One column has CA - California. All I need is CA How do I truncate this? I have approximately 400 records and would appreciate anybody's help. Thank you. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help With Reformatting | Excel Discussion (Misc queries) | |||
reformatting dates | Excel Discussion (Misc queries) | |||
Auto - reformatting | Excel Discussion (Misc queries) | |||
Reformatting a column | Excel Discussion (Misc queries) | |||
Reformatting numbers | Excel Discussion (Misc queries) |