Return text up to the second space in Microsoft Excel

by  About
       

Text :             George W. Bush

The Function:   
      =LEFT(A5,FIND("*",SUBSTITUTE(A5," ","*",LEN(A5)-LEN(SUBSTITUTE(A5," ",""))))-1)

Result:               George W.


10 thoughts on “Return text up to the second space in Microsoft Excel

  1. “Hi, I have a postcode file in Excel,
    The post codes in some cells are, for example OL16 6YH. I need somehow to take the space out, So it reads OL166YH.
    Anyway to do this in Excel?
    Thanks!!
    Matt”

  2. “Hi,
    Guido’s solution certainly works (although in my version of Excel, it needs commas rather than semi-colons, but that is probably a geogrpahical issue?)
    However, the following is perhaps simpler, and more general:
    =SUBSTITUTE(E13,”" “”,”"”")
    {Note the ‘space’ between the first set of quotes}
    This will replace ALL the spaces in the postcode, not just the first occurance.
    Hope that helps,
    Alan.”

  3. My problem regarding postcodes is completely the opposite. I have a list of postcodes, e.g. NH129AS but I want them to be in the format NH12 9AS (ie insert a space as the 5th character). Does anyone know how I can do this in Excel?

  4. “Hi Chris,
    Far beit from me to tell a native how their own postal system works, but I *believe* you might find that the actual structure of the postal codes in the UK requires you to insert a character (space) such that the last three characters are separated from the first three or four.
    This as opposed to just inserting at the fifth character.
    I could very well be wrong, or out of date, but I think that some post codes are similar to:
    CV4 7AL (somewhere in Coventry)
    Either way, I think that if your orginal format code is in cell A1, then the following should do it:
    =LEFT(A1,LEN(A1)-3)&”" “”&RIGHT(A1,3)
    Apologies if I am mistaken – it is a long way down here!
    Alan.”

  5. “Hi, I have a postcode file in Excel,

    The post codes in some cells are, for example OL16 6YH. I need somehow to take the space out, So it reads OL166YH.

    Anyway to do this in Excel? “

  6. “Guido’s solution certainly works (although in my version of Excel, it needs commas rather than semi-colons, but that is probably a geogrpahical issue?)

    However, the following is perhaps simpler, and more general:

    =SUBSTITUTE(E13,”" “”,”"”")

    {Note the ‘space’ between the first set of quotes}

    This will replace ALL the spaces in the postcode, not just the first occurance. “

  7. My problem regarding postcodes is completely the opposite. I have a list of postcodes, e.g. NH129AS but I want them to be in the format NH12 9AS (ie insert a space as the 5th character). Does anyone know how I can do this in Excel?

  8. “Far beit from me to tell a native how their own postal system works, but I *believe* you might find that the actual structure of the postal codes in the UK requires you to insert a character (space) such that the last three characters are separated from the first three or four.

    This as opposed to just inserting at the fifth character.

    I could very well be wrong, or out of date, but I think that some post codes are similar to:

    CV4 7AL (somewhere in Coventry)

    Either way, I think that if your orginal format code is in cell A1, then the following should do it:

    =LEFT(A1,LEN(A1)-3)&”" “”&RIGHT(A1,3)

    Apologies if I am mistaken – it is a long way down here! “

Leave a Reply

Your email address will not be published. Required fields are marked *


− 1 = three

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>