Identify Text within a cell and isolating values

[krindobex]

So I have spent many hours trying to solve this one on my own...I have a spreadsheet of data...approximately 16,000 lines and goes to column AP.

There is one particular column I have a lot of trouble with. My goal is to isolate the quality and quantity of storage being order or taken away.

Sometimes only storage is ordered and sometimes a server, operating system and storage is ordered.

Here are examples of what the cells could look like:
**Important to note that all the text is in one cell.
1st Example:
Dedicated ComputerPower - 0102008 - New Dedicated Intel/IBM Medium Performance (Systems)[Q:-1]
Storage - 0200024 - New Tape Backup - Std Performance (Tier-5a - Onsite/Online) (GB)[Q:-44]
OS Services - 0300001 - Windows Base Services (OS Instances)[Q:-1]
2nd Example:
Storage - 0200001 - As-is SAN High Performance (Tier-1) (GB) [Q:340]
3rd Example:
Storage - 0200009 - New SAN Standard Performance (Tier-2) (GB) [Q:40]


What has caused me the most trouble is the difference in amount of letters between types of storage. Also, the total GBs of storage differs in every order after the Q:
There are a limited amount of types of storage that can be ordered which are listed below:
Storage - 0200001 - As-is SAN High Performance (Tier-1) (GB) [Q:x]
Storage - 0200002 - As-is SAN Standard Performance (Tier-2) (GB) [Q:x]
Storage - 0200003 - As-is NAS Standard Performance (Tier-4) (GB)[Q:x]
Storage - 0200004 - As-is SAN or NAS - Capacity Oriented (Tier-3) (GB) [Q:x]
Storage - 0200008 - New SAN High Performance (Tier-1) (GB) [Q:x]
Storage - 0200009 - New SAN Standard Performance (Tier-2) (GB) [Q:x]
Storage - 0200011 - New SAN or NAS - Capacity Oriented (Tier-3) (GB) [Q:x]
Storage - 0200010 - New NAS Standard Performance (Tier-4) (GB) [Q:x]

There are installs and decoms so the Q: could be followed by #s or a '-' to indicate negative and then the # follows. The numbers in every case are within the Q: and []. I am not sure if that will help.

What I want is to create two new columns:
First column should indicated: As-is SAN, As-is NAS, New SAN or New NAS...and the second column should indicated the quantity...ie -100 or 100.

Any advice would be truly appreciated.

[dilipandey]

Hi krindobex

Welcome to the forum.

Please share a sample file with the indicative results you wish to obtain. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[krindobex]

Please find attached. I hid a couple of columns and rows to demonstrate the desired finished product. Please let me know if any other info is required.

[dilipandey]

Hi Krindobex,

See the attached file and let me know if this helps. thanks.

Sample File 1.xls

Regards,
DILIPandey

<click on below 'star' if this helps>