How do I separate 6123 Lake Washington BLVD NE,#201 Kent House,?
OK. A single formula would be little complex, so let us break it up in parts and try to solve. First we divide the text in to parts which are separated by comma. And we try to extract text before and after commas. Refer to the image below while trying all steps. Step 1: Find the position of 1st comma in this text using Find function. See the image how it is used. This actually is Find in B3 cell. Step 2: Now extract the text which is on left of this comma. Use Left function. This is Left part of text which is in B3, upto C31. i.e 28 letters of left. Step 3: Extract the text after first comma. Use Right function. This is to find text on right of first comma. LEN function finds the total length of text in B3. So LEN(B3)-C3 implies how many characters are to be found from right. Now we have effectively extracted text before and after first comma. We can repeat the same process (of finding comma, finding left hand side text and right hand side text) for 2nd comma in main text by applying the same formulas on text extracted in Step 3. Try it! Its shown in Step 4, Step 5 and Step 6. Step 2, Step 5 and Step 6 provides us texts separated by comma from the main text. You can apply the same process to separate text before and after spaces. Find space instead of comma. Hope you will be able to do it. Upvote and follow if it helped.