Excel 2013 to extract text in one cell [Closed]

Report
-
Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
-
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

Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
439
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2795 users have said thank you to us this month