Extracting date in desired format

thisisrahul

Active Member
Joined
Sep 2, 2008
Messages
285
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi, take a look here:

Get Field from Delimited Text String

For your specific example, maybe try:


Excel 2013
A
1this is, Just to test, excel possibilities of data extraction
2test, data in the below format, thanks
3please provide help, if any
4
5this is
6Just to test
7excel possibilities of data extraction
8test
9data in the below format
10thanks
11please provide help
12if any
Sheet1
Cell Formulas
RangeFormula
A5=TRIM(MID(SUBSTITUTE($A$1&","&$A$2&","&$A$3,",",REPT(" ",999)),ROWS(A$5:A5)*999-998,999))
 
Upvote 0
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.
 
Upvote 0
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.




Excel 2013
ABC
2this is, Just to test, excel possibilities of data extractionthis is
3test, data in the below format, thanksJust to test
4please provide help, if anyexcel possibilities of data extraction
5More Text, And some more.test
6Test, this, datadata in the below format
7thanks
8please provide help
9if any
10More Text
11And some more.
12Test
13this
14data
15
16
Sheet1
Cell Formulas
RangeFormula
C2=ParseIt($A$2:$A$100,ROWS(C$2:C2))
 
Upvote 0
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.
 
Upvote 0
Is this what you are trying to do? The formula in B1 is copied down and across.


Excel 2013
ABCD
1this is, Just to test, excel possibilities of data extractionthis isJust to testexcel possibilities of data extraction
2test, data in the below format, thankstestdata in the below formatthanks
3please provide help, if anyplease provide helpif any
Sheet1
Cell Formulas
RangeFormula
B1=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",999)),COLUMNS($B1:B1)*999-998,999))
 
Upvote 0
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.
 
Upvote 0
Hi, it's working on my end - do you have any formulas in the range A2:A100 that result in an error?


Excel 2013
ABC
2HSBC Global Asset Management, Macquarie Capital Securities, Fidelity worldwide InvestmentsHSBC Global Asset Management
3Motilal Oswal AMC, Enam Holding, Lucky Investment, Param Capital, Reliance LifeMacquarie Capital Securities
4Fidelity worldwide Investments
5Motilal Oswal AMC
6Enam Holding
7Lucky Investment
8Param Capital
9Reliance Life
Sheet1
Cell Formulas
RangeFormula
C2=parseit($A$2:$A$100,ROWS(C$2:C2))
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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