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

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

*1 ^{st }Example:*

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

* ***Follow the steps given below:-**

** **

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

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

*2 ^{nd} Example:*

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

**Follow the steps given below:-**

** **

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

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

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

*1 ^{st} Example:*

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

**Follow the steps given below:-**

** **

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

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

*2 ^{nd} Example:*

* *

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

**Follow the steps given below:-**

** **

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

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

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

**Follow the steps given below:-**

** **

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

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

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

**1 ^{st} 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*

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

*2 ^{nd} Example:*

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

* ***Follow the steps given below:-**

** **

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

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

**Follow the steps given below:-**

** **

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

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

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

“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”

Hi, assume E13 contains your post code

=REPLACE(E13;FIND(“” “”;E13);1;””””)

“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.”

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?

=CONCATENATE(LEFT(A28,4),” “,RIGHT(A28,3))

“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.”

“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? “

=REPLACE(Cell Address,FIND(” “,Cell Address),1,””)

“Hi, assume E13 contains your post code

=REPLACE(E13;FIND(“” “”;E13);1;””””)

http://HoloGuides.com/programming/ExcelVBA“

“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. “

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?

“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! “