Vlookup value after comma+space and return each value seperated by comma+space till the last comma+space in the cell

[sandeepsandeep]

I have a query which I am unable to solve.

I have certain Values in Sheet1 in Col A as below
991121QR5, 991121XSX, 991234SSC, 991121SDF

In Sheet2 I have following Values in Col A and B
991121QR5 100100
991121SDF 121212
991234SSC 123456
991121XSX 888888

I want to vlookup each value after comma+space with the table in Sheet2 till the last comma+space in the series.

So when I enter the following in Col A
991121QR5, 991121XSX, 991234SSC, 991121SDF

Result should look like this in Col B
100100, 888888, 123456, 121212

Thanks in advance.

 

[sandeepsandeep]

my solution does work......

Could you please upload the excel sheet.

 

[oldbrewer]

it is not allowed by the forum - try my amended formula to split out the first number and tell us the outcome

 

[Gerald Higgins]

Why not just use Excel's built in Data, Text to Columns feature, to break the source data into lots of separate columns ?

 

[Joe4]

Cross-posted here: https://chandoo.org/forum/threads/v...d-by-comma-space-till-the-last-comma-s.36769/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

Cross-posted here: https://chandoo.org/forum/threads/v...d-by-comma-space-till-the-last-comma-s.36769/

and here
https://www.excelforum.com/excel-fo...lues-till-last-comma-space-in-the-series.html
https://www.ozgrid.com/forum/forum/...ce-return-each-value-seperated-by-comma-space

 

[sandeepsandeep]

and here
https://www.excelforum.com/excel-fo...lues-till-last-comma-space-in-the-series.html
https://www.ozgrid.com/forum/forum/...ce-return-each-value-seperated-by-comma-space

<article>

Query Solved. Thread closed

https://www.excelforum.com/excel-fo...es-till-last-comma-space-in-the-series-3.html​

</article>

 

[Mike Szczesny]

Can you use something like this? Assume a, b, c, and d are in A1:A4. In other cell enter =concatenate(transpose(a1:a4&" , ")). Before you enter you need to highlight the transpose part of your formula all the way to the first ) at the end of the formula. Hit F9. Remove both { and }. Enter

[TABLE="width: 204"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]a[/TD]
[TD][/TD]
[TD]a , b , c , d , [/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

It is normal to post the solution even if you figured it yourself, to help those people who tried to help you, thus increasing the overall knowledge base of the forum...