Data Extraction from Comma Separated Values in Excel Cell

**anonymous321** · 02-02-2019, 05:40 AM

Hi Everyone!
I have attached an excel workbook which contains two sheets named "before" and "after" respectively. The "before" sheet contains the sample data to work with and "after" sheet is the desired output for few entries in "before" sheet for the sake of example. Coming to my question, The "before" sheet contains some comma separated values. Each Comma separated value contains the shipping cost in dollar and the term either "available" or "not available". I want find out the lowest possible shipping cost out those comma separated values which contain the word "available" . Here we would ignore those comma separated values which contain the word "not available" and its of no use to use. I have attached the work book and you can easily understand by check it.

Again many thanks in advance

**XOR LX** · 02-02-2019, 06:19 AM

Hi,

This array formula** should work for up to 5 occurrences of the word "available" within the string in A2:

=MIN(IFERROR(0+MID(A2,FIND("ζ",SUBSTITUTE(A2,"$","ζ",{1;2;3;4;5}))+1,MMULT(FIND("ζ",SUBSTITUTE(PROPER(A2)&"ζ",{"$","Available"},"ζ",{1;2;3;4;5})),{-1;1})-2),""))

Can easily be extended if you think there may be more than 5 such occurrences.

Note also that the array constants in the above may need amending if you're not using an English-language version of Excel.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**anonymous321** · 02-02-2019, 06:37 AM

Thanks for your response! Just asking that if I want to increase the number of occurence so should I add more numbers to this part=> 1;2;3;4;5 only?

**XOR LX** · 02-02-2019, 06:40 AM

Yes, but if you think that it's potentially going to be a very large number (20+, perhaps), then it might be better to replace the necessary array constant, i.e.:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}

with an equivalent function which generates that array.

Regards

**anonymous321** · 02-02-2019, 06:41 AM

The formula gives error "#VALUE" when applied on the cell containing "36-58 days US $2.02 Not available , Personalized Standard Shipping 20-40 days US $8.78 Not available , 7-15 days US $97.68 Available" .

**anonymous321** · 02-02-2019, 06:45 AM

On All of the following values the formula doesn't work:

SunYou Economic Air Mail 30-50 days US $0.97 Not available , POS Malaysia 20-40 days US $3.01 Not available , 10 days US $113.66 Available
POS Malaysia 20-40 days US $3.01 Not available , 7-10 days US $113.66 Available
SunYou Economic Air Mail 30-50 days US $1.00 Not available , POS Malaysia 20-40 days US $3.01 Available , 7-10 days US $59.22 Available
SunYou Economic Air Mail 30-50 days US $0.73 Not available , POS Malaysia 20-40 days US $2.63 Not available , 10 days US $113.66 Available
POS Malaysia 20-40 days US $3.01 Not available , 7-10 days US $113.66 Available
POS Malaysia 20-40 days US $3.01 Not available , 7-10 days US $113.66 Available
SunYou Economic Air Mail 30-50 days US $0.73 Not available , POS Malaysia 41-60 days US $2.42 Available , 10 days US $113.66 Available
SunYou Economic Air Mail 30-50 days US $0.52 Not available , POS Malaysia 40-56 days US $2.63 Not available , 10 days US $113.66 Available
POS Malaysia 20-40 days US $3.01 Not available , 7-10 days US $113.66 Available

**anonymous321** · 02-02-2019, 06:54 AM

The formula also give the wrong answer on the following entry

US $390000 Available , 36-58 days US $2.02 Not available , Standard Shipping 20-40 days US $8.78 Not available , 7-15 days US $97.68 Available

**XOR LX** · 02-02-2019, 12:16 PM

Are you sure you followed my instructions about entering array formulas correctly?

I don't get an error for any of those.

Regards

**AlKey** · 02-02-2019, 12:39 PM

Try this one
Enter formula in B2 and copy down

Formula:

Please Login or Register  to view this content.

**anonymous321** · 02-02-2019, 04:56 PM

Hi Alkey, does this formula works for any number of comma separated values ? Thanks. I tested your formula and it worked

**XOR LX** · 02-03-2019, 10:08 AM

@AlKey Doesn't this only work if the "Available" entries increase in value from left to right within the string?

For example, what if you change the $3.72 in:

21-36 days US $3.72 Available , Personalized Standard Shipping 26-43 days US $3.99 Available , 9-20 days US $133.00 Available

to, for example, $10.00, i.e.:

21-36 days US $10.00 Available , Personalized Standard Shipping 26-43 days US $3.99 Available , 9-20 days US $133.00 Available

Your formula now returns 10, instead of the correct 3.99.

Perhaps your assumption was right, but obviously the OP would need to confirm.

Regards

**AlKey** · 02-03-2019, 12:23 PM

@XOR LX
I don't really understand the purpose of manipulating the original data. The logic seams to be is to extract the value of the first smallest value that is Available.

**XOR LX** · 02-03-2019, 02:36 PM

What do you mean by "first smallest"? "First" or "Smallest" would make sense, but together I'm not sure what you mean.

Regards

Data Extraction from Comma Separated Values in Excel Cell

LinkBack

Thread Tools

Rate This Thread

Display

Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Re: Data Extraction from Comma Separated Values in Excel Cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sum values using vlookup with lookup cell containing comma separated values

Filtering data from another sheet if cell has values separated by comma

Doing a look up on a cell with comma separated values

[SOLVED] Using comma separated values in a single cell

Function for Comma-Separated Values within Cell

Format / copy paste comma separated data in one cell to other with other values

Resolved >>> Comma separated values in a cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions