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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
It's not clear what you want to do apart from create a database. In what, Access, FoxPro, Oracle?
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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
Back
Top