Excel 2013 to extract text in one cell

Closed
Fiddler123 - Sep 4, 2019 at 12:01 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Sep 5, 2019 at 11:40 AM
Hi, I want to extract in the right column, a part of the left column, for instance: see below 2 lines for each line at the right column I want to extract only Unit 3 and next line Unit 22..see cell: A1 and A2 below:
Cell A1:Payment rcvd: John Doe, Unit 3 - Conf #23206689, Jul 30 to Aug 01, 2019, Inv #32484 XXXX XXXX XXXX 7028
Cell A2:Payment rcvd: Joanie Stuarts Proulx, Unit 22 - Conf #23313004, Aug 23 to Aug 25, 2019, Inv #32556 XXXX XXXX XXXX 3270
I want to see lonly Unit 3 at cell A2 and Unit 22 at cell B2, how do I do that? thank you.
Lucie

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Sep 5, 2019 at 11:40 AM
Hi Lucie,

You do that by looking at patterns in your strings of data. In your case "Unit #" is found between the first "," and "-". Then use the FIND function to determine the position of those characters. Once you know that, use the MID function to extract the "Unit #" from the data string. Putting that all together and you will get:

=MID(A1,FIND(",",A1)+2,FIND("-",A1)-FIND(",",A1)-3)

Best regards,
Trowa
2