Extract text from Paragraph

0

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.

Discussion

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

0

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

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.

0

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.