Extract text from Paragraph


Hello, I have the following form that is submitted by customers and need to extract specific information from it. I always get the same fields of information and would like to separate each field on different cells: Purchase date, purchase time, receipt number and comment. I would appreciate all the help! Thanks in advance!!!

The paragraph text below for reference:

Purchase Date: 07/11/2018

Purchase Time: 8:00AM CST

Receipt Number: 123456

Comment: Hello, I would like a refund for this transaction because the items did not work out well for me.



Is this all loaded into a single cell? Can you edit your question and upload it in a sample file?
don (rep: 1960) Jul 11, '18 at 11:52 am
File uploaded! :)
hillscalli Jul 11, '18 at 2:37 pm
Add to Discussion



Use Ctrl + J as the delimiter for Text-to-Columns.

Follow these steps:

  1. Select the cells with the data.
  2. Click Text-to-Columns
  3. Choose Delimited and hit Next.
  4. Check Other
  5. In the box next to Other, type Ctrl + J
  6. Hit Finish.

Once you do this, it should split the data up and leave you with, potentially, one empty column that you can then delete.

Ctrl + J stands for "Carriage Return" and you get this kind of thing in a cell when you import certain types of data or when you type Alt + Enter within a cell.


Please try these three formulas. They were tested in the workbook you posted.

[B2] =TRIM(MID(LEFT($A2,FIND(CHAR(10),$A2)-1), FIND("Date:", $A2)+LEN("Date:"),100))
[C2] =TRIM(MID(LEFT($A2,FIND(CHAR(160),SUBSTITUTE($A2,CHAR(10),CHAR(160),3))+1),FIND("Number:",$A2)+LEN("Number:"),100))
[D2] =MID($A2,FIND("Comment:",$A2)+LEN("Comment:")+1,100)

Note that the number 100 indicates the maximum length of string to be extracted. Obviously that is far too much for the date and the receipt number - excess is ignored - but it may not be enough for the comments unless you want them truncated.


Answer the Question

You must create an account to use the forum. Create an Account or Login