Excel 2013 to extract text in one cell

Report
-
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 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
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4024 users have said thank you to us this month