Reformatting

  • Thread starter Thread starter ILoveMyCorgi
  • Start date Start date
I

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.
 
hi
1. =MONTH(D3)&DAY(D3)&YEAR(D3)
2. =SUBSTITUTE(F3,"-","")
3. =LEFT(D5,2)


adjust cell reference to suit your data;

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

FSt1 said:
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 said:
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.
Click to expand...
Click to expand...
 
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
 
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

L. Howard Kittle said:
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 said:
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.
Click to expand...


.
Click to expand...
 
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 said:
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

L. Howard Kittle said:
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 said:
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.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top