Copyright Year Re-Formatting

[RefLib1978]

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

 

[David Biddulph]

One option:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,",",""),".",""),"?",""),"-",""),"]",""),"[",""),"c","")

 

[RefLib1978]

Thank you! Thank you! Thank you! You made my day. It worked like a charm.

One option:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,",",""),".",""),"?",""),"-",""),"]",""),"[",""),"c","")
--
David Biddulph

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

 

[Peo Sjoblom]

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

Thank you! Thank you! Thank you! You made my day. It worked like a charm.

One option:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,",",""),".",""),"?",""),"-",""),"]",""),"[",""),"c","")
--
David Biddulph

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