Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copyright Year Re-Formatting
Hi -
I thought I saw the solution here, but I can't seem to find it again so I'm submitting my own version. Please apologies for any duplication. I've been looking for what feels like eons. Here is the problem. I've imported a text file into Excel. All of it is fine except for the format of the year. There is no consistency and very rarely are they just 4 numbers together. They look like: c1982. [1973, c1966] [1942] c1995- [1964?, c1962] 1969] 1973 [c1972] As you can see it's all over the place. Is it possible to write a formula or macro that can delete everything but the numbers in this column? It's in column D. Any help or advice - even if you just want to direct me to the question that was already submitted - would be really appreciated. Thanks, Celina |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copyright Year Re-Formatting
One option:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(D1,",",""),".",""),"?" ,""),"-",""),"]",""),"[",""),"c","") -- David Biddulph "RefLib1978" wrote in message ... Hi - I thought I saw the solution here, but I can't seem to find it again so I'm submitting my own version. Please apologies for any duplication. I've been looking for what feels like eons. Here is the problem. I've imported a text file into Excel. All of it is fine except for the format of the year. There is no consistency and very rarely are they just 4 numbers together. They look like: c1982. [1973, c1966] [1942] c1995- [1964?, c1962] 1969] 1973 [c1972] As you can see it's all over the place. Is it possible to write a formula or macro that can delete everything but the numbers in this column? It's in column D. Any help or advice - even if you just want to direct me to the question that was already submitted - would be really appreciated. Thanks, Celina |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copyright Year Re-Formatting
Thank you! Thank you! Thank you! You made my day. It worked like a charm.
"David Biddulph" wrote: One option: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(D1,",",""),".",""),"?" ,""),"-",""),"]",""),"[",""),"c","") -- David Biddulph "RefLib1978" wrote in message ... Hi - I thought I saw the solution here, but I can't seem to find it again so I'm submitting my own version. Please apologies for any duplication. I've been looking for what feels like eons. Here is the problem. I've imported a text file into Excel. All of it is fine except for the format of the year. There is no consistency and very rarely are they just 4 numbers together. They look like: c1982. [1973, c1966] [1942] c1995- [1964?, c1962] 1969] 1973 [c1972] As you can see it's all over the place. Is it possible to write a formula or macro that can delete everything but the numbers in this column? It's in column D. Any help or advice - even if you just want to direct me to the question that was already submitted - would be really appreciated. Thanks, Celina |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copyright Year Re-Formatting
Another way
=--MID(D1,MIN(FIND({1,2,3,4,5,6,7,8,9},D1&"1,2,3,4,5, 6,7,8,9")),4) -- Regards, Peo Sjoblom "RefLib1978" wrote in message ... Thank you! Thank you! Thank you! You made my day. It worked like a charm. "David Biddulph" wrote: One option: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(D1,",",""),".",""),"?" ,""),"-",""),"]",""),"[",""),"c","") -- David Biddulph "RefLib1978" wrote in message ... Hi - I thought I saw the solution here, but I can't seem to find it again so I'm submitting my own version. Please apologies for any duplication. I've been looking for what feels like eons. Here is the problem. I've imported a text file into Excel. All of it is fine except for the format of the year. There is no consistency and very rarely are they just 4 numbers together. They look like: c1982. [1973, c1966] [1942] c1995- [1964?, c1962] 1969] 1973 [c1972] As you can see it's all over the place. Is it possible to write a formula or macro that can delete everything but the numbers in this column? It's in column D. Any help or advice - even if you just want to direct me to the question that was already submitted - would be really appreciated. Thanks, Celina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copyright Symbol | Excel Discussion (Misc queries) | |||
Copyright Question | Excel Discussion (Misc queries) | |||
excel copyright | Charts and Charting in Excel | |||
Formula for Fiscal Year Conditional Formatting | Excel Discussion (Misc queries) | |||
Copyright in Footer | Excel Worksheet Functions |