Reformatting

[ILoveMyCorgi]

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.

 

[FSt1]

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]

Thank you so much! I hope you have a great evening.
Susan

hi
1. =MONTH(D3)&DAY(D3)&YEAR(D3)
2. =SUBSTITUTE(F3,"-","")
3. =LEFT(D5,2)


adjust cell reference to suit your data;

regards
FSt1

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.

 

[L. Howard Kittle]

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

 

[FSt1]

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/usps_abbreviations.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

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

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.

.

 

[L. Howard Kittle]

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

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/usps_abbreviations.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

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

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.

.