Specific extration

Kabous07

New Member
Joined
May 8, 2013
Messages
34
Good day all clever ones, i believe i might have a thinker here.

I am struggling with something. I have over 3000 cells, like the below, each with a different code and here below is what i want to do. I want excel to "scan" from A2-A3002 in B2-B3002 respectively. The results in column B must be a description(Which i obviously have to create a data base, like for instance "ANL-Air cooled chiller" and then ANL***P-Air cooled chiller with low head pump)


Unit
Description
ANL020
Air cooled chiller
ANL020P
Air cooled chiller with low head pump
ANL020A
Air cooled chiller with low head pump and buffer tank
ANL020H
Air cooled heatpump
ANL020HP
Air cooled heatpump with low head pump
ANL020HA
Air cooled heatpump with low head pump and buffer tank
ANL030
Air cooled chiller
ANL030P
Air cooled chiller with low head pump

<tbody>
</tbody>

Please let me know. I really do not want to type(copy paste) everything over 3000 times
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
It's not clear what you want to do apart from create a database. In what, Access, FoxPro, Oracle?
 

Kabous07

New Member
Joined
May 8, 2013
Messages
34
Hi Special-K99 , Sorry.

So currently in column A, i have those model numbers, ANL****. What i want to do is i want to give those model numbers a description in column B, but the discription differs for each character at the end of the model number, for eg.

******H means Air cooled heatpump
******A means Air cooled chiller with internal low head pump and buffer tank
******P means Air cooled chiller with internal low head pump

and these are only some of the descriptions. So basically what i want excel to do is search the model number in cell A1, then it must be able to see there is a "H"(or an "A" or and "P" etc) at the end of the model number, when it notice there is an "H" at the end it must give me a description in cell B1, saying Air cooled heatpump. Here below is a table were i have the data. So i want i formula that i put into cell B1 to search the unit(column A) for H, HM, HA, HAM, HP...etc. then in this respective cell B it must give me the description.

Unit
DescriptionDataDescription
ANK020HHAir cooled heatpump
ANK020HMHAAir cooled heatpump with internal low head pump and buffer tank
ANK020HAHAMSingle Phase Air cooled heatpump with internal low head pump and buffer tank
ANK020HAMHPAir cooled heatpump with internal low head pump
ANK020HPHAPSingle Phase Air cooled heatpump with internal low head pump
ANK020HPM
ANK030H
ANK030HM
ANK030HA
ANK030HAM
ANK030HP
ANK030HPM
ANK040H
ANK040HM

<tbody>
</tbody>
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
Do the model numbers always come with 6 characters?

Hi Special-K99 , Sorry.

So currently in column A, i have those model numbers, ANL****. What i want to do is i want to give those model numbers a description in column B, but the discription differs for each character at the end of the model number, for eg.
 

Kabous07

New Member
Joined
May 8, 2013
Messages
34

ADVERTISEMENT

Re: Specific extraction

Do the model numbers always come with 6 characters?

Hi mfexcel.

Sometimes it is even more. It will probably get up to 15 characters. Where each character have its own description.
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
Re: Specific extraction

Do they follow a specific pattern then? e.g. would it be always a number or even better always "0" just before the data you are looking for?


Hi mfexcel.

Sometimes it is even more. It will probably get up to 15 characters. Where each character have its own description.
 

Kabous07

New Member
Joined
May 8, 2013
Messages
34

ADVERTISEMENT

Re: Specific extraction

Sometimes they will have have a number(either a "2" or a "4") before the data(description), but 90% will be a "0" before. For example

ANL020A
ANK080P
NRL0500P1
NRL0700YP1
NS1802PB
ANL152HA
ANL202HB
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
Re: Specific extraction

well, i see in your examples that there may be cases end with a number. Would it be more than one digit in the end? Any other scenarios?


Sometimes they will have have a number(either a "2" or a "4") before the data(description), but 90% will be a "0" before. For example

ANL020A
ANK080P
NRL0500P1
NRL0700YP1
NS1802PB
ANL152HA
ANL202HB
 

Kabous07

New Member
Joined
May 8, 2013
Messages
34
Re: Specific extraction

well, i see in your examples that there may be cases end with a number. Would it be more than one digit in the end? Any other scenarios?

Uuuhm, there is ones like this: NRP1800E4
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
Re: Specific extraction

i mean how many digit would it be??? always one digit or may more than one? e.g. NPR1024E40

On the other hand, there is no "HM" or "E4" in your data table posted...

HAir cooled heatpump
HAAir cooled heatpump with internal low head pump and buffer tank
HAMSingle Phase Air cooled heatpump with internal low head pump and buffer tank
HPAir cooled heatpump with internal low head pump
HAPSingle Phase Air cooled heatpump with internal low head pump

<colgroup><col><col></colgroup><tbody>
</tbody>


are you sure you have a Data Table that meets all your cases???


Uuuhm, there is ones like this: NRP1800E4
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,705
Members
414,401
Latest member
grenona2020

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