MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel string parsing

with 14 comments

Parsing strings isn’t the easiest thing to do in Excel 2007 or Excel 2008 but it is an important thing to know how to do. You’ll learn how to parse a set names into first, middle and last names. You can find the data set for these examples at the bottom of the blog post.

Written by maclochlainn

February 27th, 2009 at 10:15 pm

14 Responses to 'Excel string parsing'

Subscribe to comments with RSS or TrackBack to 'Excel string parsing'.

  1. I thought this has helped me alot to understand Parsing and it is simple and easy to understand. The examples helped me and I feel like I can do this a lot better now.

    Kyle Shurtliff

    6 Mar 09 at 9:02 am

  2. Thanks – clear, used parsing in Lotus 123, just catching up !!

    Mike Egerton

    1 Jun 09 at 12:42 am

  3. I found this page to be immensely useful in figuring out how to parse strings from the right. Logical and well thought out.

    Bob Jensen

    11 Mar 10 at 3:08 pm

  4. This was clear and consise. Thank you!

    Tony Grace

    21 Oct 10 at 11:39 am

  5. Very well written, thank you. This article saved me a ton of time!

    Brian S

    8 Nov 10 at 11:46 am

  6. A shorter version for the left substring parsing:
    =LEFT(A1,IFERROR(FIND(” “,A1),LEN(A1)))

    Mc

    20 Jun 11 at 9:30 am

  7. Great post! helped me quickly.
    Thank you so much.

    Pal Ram

    30 Aug 11 at 7:30 pm

  8. This was a great in depth tutorial that really guided me through why you needed the IFERROR and not just the FIND function. Thank You.

    Izzy Haggen

    20 Sep 11 at 12:06 pm

  9. Thanks – I used your approach (nested FIND functions) to parse the file name out of a file path (delimiting on “/”) without using VB.

    This approach only works if you have a reasonably low limit to the number of segments you’re expecting. In my case, using the pasted formula below, the limit is six delimiters (folders). I’ll include a copy in case it helps someone reading these comments :)

    To parse out the last value in a delimited list of 6 or fewer values, use this formula but replace A1 with the location of your string, and “/” with your actual delimiter:

    =LEFT(RIGHT(A1, LEN(A1)-IFERROR(FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, 1)+1)+1)+1)+1)+1), IFERROR(FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, 1)+1)+1)+1)+1), FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, 1)+1)+1)+1)))), LEN(RIGHT(A1, LEN(A1)-IFERROR(FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, 1)+1)+1)+1)+1)+1), IFERROR(FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, 1)+1)+1)+1)+1), FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, 1)+1)+1)+1)))))-4)

    Jeff Geurts

    20 Nov 11 at 4:47 pm

  10. Very useful. Thanks for posting. @JeffGeurts: It was easy for me to adapt your very long formula to grab each Web Service name from a list of long URLs. I didn’t have an extension to strip so I removed the -4 at the end. I also updated the start position from 1 to 50 in each location that had “, A1, 1)” which is a handy way to reduce the length if you have a common base URL that you can just discard.

    zephans

    6 Sep 12 at 10:58 pm

  11. Incredibly helpful. Thanks for this post.

    mkubica

    26 Oct 12 at 7:23 am

  12. Thank you *so much* for this post. I have trouble parsing strings myself, and a student I was tutoring needed to learn it for a test. I used your page as instructional material. You are just excellent at explaining such difficult logical concepts in a way the student could learn them for the test and apply them in real time. I really appreciate the work you put into the post. You have taught me a lot. Thanks again!

    Monika

    24 Apr 13 at 4:14 am

  13. Hi,
    I have been trying to understand this subject and not having one ounce of programming skill I am still struggling despite the detialed explanation.

    I simply want to parse this string;

    1. Burlington Gardens 100

    so that;
    1. goes to cell A1, Burlington Gardens goes to cell A2 and 100 goes to cell A3

    The 2 text words (Burlington Gardens) may sometimes be one word or three, but a space character is always the delimiter.

    CNXTim

    24 Aug 13 at 7:00 pm

  14. Mormon prophets! Woo-woo!

    Greg

    18 Dec 13 at 12:55 pm

Leave a Reply