Return text up to the second space in Microsoft Excel

 

 

In this article we will learn how to return text up to the second space in Microsoft Excel.

For this, we will be using the combination of Excel “FIND”, “SUBSTITUTE “and “LEN” functions.

FIND: This function in Microsoft Excel returns the position of a specified character or sub-string within a string or text.

 
 

The function arguments / syntax are:

001. Find formula in Excel

 

Let us take an example of FIND function:

We have some name in column A and column B is for the result. We will use the “FIND” function to get the desired output.

 

002. Find Function Example-i

 

1st Example:

In this example we will find the place number of the character “Aaron”.

 

003. Find Function Example-ii

 
 Follow the steps given below:-
 

  • Enter the function in cell B7
  • =FIND(“Aaron”,A7)
  • Press Enter

 
004. Find Function Example-iii

 

The function will return 1 because “Aaron” is available on 1st place on the cell text string.

 

2nd Example:

In this example we will take case sensitive text then we will see how the “FIND” function performs.

 

005. Find Function Example 2 - i

 

Follow the steps given below:-

 

  • Enter the function in cell B8
  • =FIND(“kevin”,A8)
  • Press Enter

006. Find Function Example 2 - ii

 

In this example the function will return #VALLUE! Error because “FIND” function works on case sensitive text. We have mentioned “kevin” and the same text is available in upper case in the cell.

 

LEFT: In Microsoft Excel, LEFT function is used to extract the leftmost characters from a string. In addition, the number of characters returned from the left side is based on the number of characters we specify. It is generally used to separate text, like initials, first name, zip code, area code etc.

 

The function arguments / syntax are:

 

007. LEFT formula in Excel

 

Let us take an example of LEFT function:

 

We have some text in column A and column B is for the result. We will use the “LEFT” function and extract the leftmost character.

 

008. LEFT Function Example 1 - i

 

1st Example:

 

In this example we will learn how to extract 3 letters from left.

 

009. LEFT Function Example 1 - ii

 

Follow the steps given below:-

 

  • Enter the function in cell D7
  • =LEFT(A7,3)
  • Press Enter 

010. LEFT Function Example 1 - iii

 

In this example, “LEFT” function will return “The” from the text “The sample Book”.

 

2nd Example:

 

In this example we will not give anything in the 2nd argument and then see how the  “LEFT” performs.

 

011. LEFT Function Example 2 - i

 

Follow the steps given below:-

 

  • Enter the function in cell D8
  • =LEFT(A8,3)
  • Press Enter

012. LEFT Function Example 2 - ii

 

By default, the function has returned “S” because we have not provided anything in the 2nd argument.

 

SUBSTITUTE:function is used to replace old text with new text.

 

The function arguments / syntax are:

 

013. SUBSTITUE formula in Excel

 

 

Let us take an example of SUBSTITUTE function:

 

We have Text in column A and column B is for the result. We want “Bubble” instead of “Tuttle” so we can use the “SUBSTITUTE” function.

 

014. SUBSTITUTE Function Example 1 - i

 

Follow the steps given below:-

 

  • Enter the function in cell D7
  • =SUBSTITUTE(A7,”t”,”b”)
  • Press Enter

015. SUBSTITUTE Function Example 1 - ii

 

In this example, the function will return bubble instead of tuttle. So, in this way we can use the “SUBSTITUTE” function.

 

LEN: This function returns the length of character from text string.

 

The function arguments / syntax are:

 

016. LEN formula & its syntax in Excel

 

Let us take an example of LEN function:

 

We have some random Text in column A and column B is for the result. We will use the LEN function and then see how this function performs.

 

018. LEN function Example 1 - ii

 

1st Example:

 

In this example we will consider a Text string then will count how many characters are there in the text with the help of the “LEN” function.

 

Follow the steps given below:-

 

  • Enter the function in cell B7
  • =LEN(A7)
  • Press Enter      

019. LEN function Example 1 - iii

 

As you can see in the above example, the function will return 5 because 5 characters are there in “Excel”.

 

2nd Example:

We will take a blank cell in this example and then observe how the LEN function performs.

 

020. LEN function Example 2 - i

 

 Follow the steps given below:-

 

  • Enter the function in cell B8
  • =LEN(A8)
  • Press Enter

021. LEN function Example 2 - ii

 

The function will return 0 because cell A8 is blank. So, in this way we can use the “LEN” function.

 

Let us take an example to understand how to return text up to the second space in Microsoft excel. We will be using the combination of “SEARCH”, “LEFT” “LEN” and “SUBSTITUTE” functions.

 

We have some names in column A and column B is for the result. We will use these functions to return text up to the second space character in the string.

 

022. How to return text up to second space in Excel Example 1 - i

 

Follow the steps given below:-

 

  • Enter the function in cell B7
  • =LEFT(A7,FIND(“*”,SUBSTITUTE(A7,” “,”*”,LEN(A7)-LEN(SUBSTITUTE(A7,” “,””))))-1)
  • Press Enter

023. How to return text up to second space in Excel Example 1 - ii

 

Copying the function from cell B7 to the entire range of B8:B11 to get the desired results.

 

024. How to return text up to second space in Excel Example 1 - iii

 

As you can see, the functions has returned the text up to the second space character in Microsoft excel.

 

If you liked our blog, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

 

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.



Example:


12 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 *

*

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>