Extract mixed strings of capitals and numbers from text

Lolcc

New Member
Joined
Jan 9, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi, I'm trying to remove mixed strings of capitals and dates / numbers from other text. I have a lot of data showing information in the following format:

GLENKINCHIE 1986 DISTILLERS EDITION Limited edition single Lowland malt whisky. Special release G/273-7-D. 1L, 43% volume, in carton. DALWHINNIE 1988 DISTILLERS EDITION Single Highland malt whisky, bottled 2003. Special release D.SK.312. 70cl, 43% volume, in carton. DALWHINNIE 15 YEAR OLD CENTENARY EDITION Single Highland malt whisky. 70cl, 43% volume. In carton. 3 bottles

For the example shown, I would want to extract GLENKINCHIE 1986 DISTILLERS EDITION, DALWHINNIE 1988 DISTILLERS EDITION, DALWHINNIE 15 YEAR OLD CENTENARY EDITION into the same cell.

Is this at all possible to do? I had read a great forum (#684934) where Rick Rothstein provided a UDF for someone requesting to remove just capitals which worked really well but missed the numbers. Someone had asked for an example to also extract numbers as well though the amended UDF did not work when I applied this in VB.

Any ideas for what might make this work. I appreciate within the text it could be either a date (e.g. 1986) or a # YEAR (e.g. 15 YEAR). I'm new to VB though loving what it can do with a basic application.

Thanks Lolcc
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,021
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
Of course better results probably could be obtained if you could get the original data in a more standard format (eg always space after full stop, all words separated etc) :)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,143,677
Messages
5,720,257
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top