Excel 2013 to extract text in one cell

- - Latest reply: TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
- 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
See more 

1 reply

Best answer
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379
2
Thank you
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

Say "Thank you" 2

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

CCM 5622 users have said thank you to us this month

Respond to TrowaD