Extracting date in desired format

thisisrahul

Active Member
Joined
Sep 2, 2008
Messages
278
I've data in the below format:-

this is, Just to test, excel possibilities of data extraction
test, data in the below format, thanks
please provide help, if any

<tbody>
</tbody>

i want the above data to be extracted in the below format:-

this is

<tbody>
</tbody>
Just to test

<tbody>
</tbody>
excel possibilities of data extraction

<tbody>
</tbody>
test

<tbody>
</tbody>
data in the below format

<tbody>
</tbody>
thanks

<tbody>
</tbody>
please provide help

<tbody>
</tbody>
if any

<tbody>
</tbody>

<tbody>
</tbody>


The above data is comma separated.


_______
Rahul
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, take a look here:

Get Field from Delimited Text String

For your specific example, maybe try:

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">this is, Just to test, excel possibilities of data extraction</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">test, data in the below format, thanks</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">please provide help, if any</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFF00;;">this is</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Just to test</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">excel possibilities of data extraction</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">test</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">data in the below format</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">thanks</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">please provide help</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">if any</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A5</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">$A$1&","&$A$2&","&$A$3,",",REPT(<font color="Purple">" ",999</font>)</font>),ROWS(<font color="Green">A$5:A5</font>)*999-998,999</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

thisisrahul

Active Member
Joined
Sep 2, 2008
Messages
278
Thanks FormR,
I tried using delimited, but post that i had to use transpose also which is cumbersome.

The above formula is working fine for 3 rows, how to apply this formula for say 100 rows.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
The above formula is working fine for 3 rows, how to apply this formula for say 100 rows.

I can't think of an easy way with formula - but you could try this UDF:

Code:
Function ParseIt(r As Range, Num As Long) As String
On Error Resume Next
 ParseIt = Trim(Split(Join(Application.Transpose(r.Value), ","), ",")(Num - 1))
End Function

To use:

1. With your spreadsheet open, press ALT+F11 to open the VBE.
2. Click "Insert" > "Module" on the menu bar.
3. Copy and paste the code into the blank window on the top right hand side.
4. Press ALT+Q to close the VBE
5. Save your workbook as a macro enabled workbook (xlsm)
6. Use just like any another worksheet function as demonstrated below.



<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">this is, Just to test, excel possibilities of data extraction</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">this is</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">test, data in the below format, thanks</td><td style="text-align: right;;"></td><td style=";">Just to test</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">please provide help, if any</td><td style="text-align: right;;"></td><td style=";">excel possibilities of data extraction</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">More Text, And some more. </td><td style="text-align: right;;"></td><td style=";">test</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Test, this, data</td><td style="text-align: right;;"></td><td style=";">data in the below format</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">thanks</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">please provide help</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">if any</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">More Text</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">And some more.</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Test</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">this</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=ParseIt(<font color="Blue">$A$2:$A$100,ROWS(<font color="Red">C$2:C2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

thisisrahul

Active Member
Joined
Sep 2, 2008
Messages
278

ADVERTISEMENT

Thanks FormR,
the above UDF is not working, after applying it is giving me blank values only.
 

thisisrahul

Active Member
Joined
Sep 2, 2008
Messages
278

ADVERTISEMENT

below is the data, i am using:-

ABC
HSBC Global Asset Management, Macquarie Capital Securities, Fidelity worldwide Investments=parseit($A$2:$A$100,ROWS(C$2:C2))
Motilal Oswal AMC, Enam Holding, Lucky Investment, Param Capital, Reliance Life=parseit($A$2:$A$100,ROWS(C$2:C3))

<tbody>
</tbody>

Let me know if i am doing something wrong.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Is this what you are trying to do? The formula in B1 is copied down and across.

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">this is, Just to test, excel possibilities of data extraction</td><td style="color: #333333;background-color: #FFFF00;;">this is</td><td style="color: #333333;;">Just to test</td><td style="color: #333333;;">excel possibilities of data extraction</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">test, data in the below format, thanks</td><td style="color: #333333;;">test</td><td style="color: #333333;;">data in the below format</td><td style="color: #333333;;">thanks</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">please provide help, if any</td><td style="color: #333333;;">please provide help</td><td style="color: #333333;;">if any</td><td style="color: #333333;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">$A1,",",REPT(<font color="Purple">" ",999</font>)</font>),COLUMNS(<font color="Green">$B1:B1</font>)*999-998,999</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

thisisrahul

Active Member
Joined
Sep 2, 2008
Messages
278
FormR,
I had got the same result using delimited and transpose but its not fulfilling the requirements. It is taking more steps to get the data in desired format. I think VBA code will be most suited way to resolve this. but the code that you shared is not working, help me how to use that VB code.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, it's working on my end - do you have any formulas in the range A2:A100 that result in an error?

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">HSBC Global Asset Management, Macquarie Capital Securities, Fidelity worldwide Investments</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">HSBC Global Asset Management</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">Motilal Oswal AMC, Enam Holding, Lucky Investment, Param Capital, Reliance Life</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">Macquarie Capital Securities</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Fidelity worldwide Investments</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Motilal Oswal AMC</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Enam Holding</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Lucky Investment</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Param Capital</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Reliance Life</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=parseit(<font color="Blue">$A$2:$A$100,ROWS(<font color="Red">C$2:C2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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