How To Separate Words In Excel Using Formula
Separating words are of import in Excel to extract information from your worksheet. There are many methods of doing that. Here, in this commodity, we will see "How to Separate Words in Excel Using Formula". We will utilize many different examples to demonstrate the processes like separating words past the delimiter, separating text from numbers, divide different format names from a text string, etc.
Carve up Words by Delimiter using Formula
In this example, nosotros volition work with words with the delimiters. Suppose, we have a list of Items along with other information like color and size. These are written in a cell with combinations of the colon. Our goal is to divide these words into different columns to brand the data more precise. Let`s see the below tabular array.
In the above table, yous can see the clarification of different items. Here, nosotros create extra iii columns to extract the exact data in each specific cavalcade from the Detail Description column.
First of all, we are going to extract the item names from the Detail Clarification. The process of doing this is given below,
- Write down the formula,
<lawmaking>=LEFT(B2,SEARCH("-",B2,one)-1) in cell C2.
- At present press Enter. You will see the item name from jail cell B2 appears in cell C2.
- Drag the formulated cell C2 downwards to copy this formula for the rest of the cells of the Item column.
Now, to extract the color name follow the below steps.
- Get-go, write downward the formula,
<lawmaking>=MID(B2, SEARCH("-",B2) + 1, SEARCH("-",B2,SEARCH("-",B2)+i) – SEARCH("-",B2) – 1) in cell D2.
- After pressing Enter yous volition get to see the below result.
Elevate the formulated cell D2 downwards to copy this formula for the residuum of the Colour column.
To extract Size nosotros will use a unlike formula that uses RIGHT, LEN, and SEARCH functions together. The procedure for this is given beneath.
- Write the formula
<lawmaking>=Correct(B2,LEN(B2)-SEARCH("-",B2, SEARCH("-",B2) + 1)) in prison cell E2.
- Later on pressing Enter you will get the below issue.
- Like earlier, drag the formulated cell downwards to re-create this formula for the residuum of the cells and you volition get the below event.
Separating Text from Numbers
In many cases, you volition find text with numbers and from that, you may need the separate the texts. In that location are few formulas for doing that. Permit's await into the beneath table. From this tabular array, nosotros will extract the cities name and postcodes separately.
Extracting Words and Numbers by counting Characters
One of the processes of doing that is counting the full digits in a cell and from them extracting the number and word that you lot want.
- In jail cell D2,write down the formula,
=RIGHT(B2,SUM(LEN(B2) - LEN(SUBSTITUTE(B2, {"0","1","ii","3","four","5","six","7","8","ix"},""))))and press Enter.
- Elevate this formulated cell downwards for the remainder of the cells of column Post Lawmaking.
- Now to extract Cities name write down the formula,
<code>=LEFT(B2,LEN(B2)-LEN(D2)) in prison cell C2 and press Enter.
- Past dragging this cell downwards you will get all the cities names in the Metropolis column.
Finding the position of the First digit of a String
This procedure involves finding the position of the 1st digit of a number in a prison cell. At that place will be a dissever column to find the position of the onest digit.
- In cell C2, write down the formula
<code>=MIN(SEARCH({0,one,2,iii,iv,v,6,vii,8,nine},B2&"0123456789″)) and press Enter. After this double-click, the Fill Handle choice or drag down the formulated from C2 to C8 to copy this formula for the residuum of the cell. You lot will go to see the beneath result.
- Now to excerpt cities name write the formula,
<code>=LEFT(B2, C2-1) in cell D2 and copy this formula for the rest of the cells of Column City. You will become the below result after that.
- For extracting the numbers apply the formula
<code>=RIGHT(B2, LEN(B2)-C2+one) in cell E2 and drag it downward for the rest of the cells of column Postcode.
Separate Words in Excel Using Formula (Working With Unlike Names)
In this example, we volition be working with different format names. From these names, nosotros will be extracting first names, middle names, last names, suffixes of names. Let`due south look into the below film then that you volition get an thought of what we are going to practice.
Like Readings
- How to Separate Ii Words in Excel (half-dozen Piece of cake Ways)
- Split text into multiple cells in Excel
- How to Split Cord by Length in Excel (8 Ways)
Separating Names from a Prison cell Which Has "No Middle Names"
Here we are going to excerpt the ist and last name from a cell which has no centre names. Look into the below table to get a clear thought near the formulas and the results.
| Full Proper noun | Rupom Chowdhury |
| Description | No Middle Names |
| Formula For the First Name | =LEFT(B2, SEARCH(" ",B2,1)) |
| Start Name Result | Rupom |
| Formula For the Last Proper name | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1)) |
| Last Name Result | Chowdhury |
In this example, you lot are going to see the extraction of 3 names from a cell. Follow the process given in the below table for getting the desired result.
| Total Name | Niloy North. Costa |
| Description | 1 Middle Name |
| Formula For the First Name | =LEFT(B2, SEARCH(" ",B2,i)) |
| Start Proper name Outcome | Niloy |
| Formula For the Middle name | =MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-SEARCH(" ",B2,1)) |
| Eye Proper noun Result | N. |
| Formula for the Concluding Name | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1)) |
| Final Name Issue | Costa |
This example shows the extraction of names having ii eye names. The first and last names volition also be extracted along with the 2 center names.
| Full Name | Ahmed J.H. Likhon |
| Description | 2 Center Names |
| Formula For the Starting time Name | =LEFT(B2, SEARCH(" ",B2,1)) |
| First Proper noun Event | Ahmed |
| Formula For the Middle proper name | =MID(B2,SEARCH(" ",B2,i)+one,SEARCH(" ",B2,SEARCH(" ",B2,SEARCH("",B2,1)+ane)+1)-SEARCH(" ",B2,1)) |
| Eye Name Outcome | J.H. |
| Formula for the Final Proper noun | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH("",B2,SEARCH(" ",B2,ane)+1)+ane)) |
| Last Name Result | Likhon |
Separating Names from United states of america Proper noun Format (Comma with the Final Name in 1st place)
If whatsoever name is written in the Usa name format you can extract the first, last, and center names by post-obit the formulas stated in the below table.
| Full Proper noun | Khan, Siam Hasan |
| Description | Comma with the Last Name in ist Place |
| Formula For the First Name | =MID(B2,SEARCH(" ",B2,1)+1,SEARCH(" ",B2,SEARCH(" ",B2,one)+1)-SEARCH(" ",B2,1)) |
| Beginning Name Upshot | Siam |
| Formula For the Eye proper noun | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+ane)) |
| Middle Name Issue | Hasan |
| Formula for the Last Proper name | =LEFT(B2, SEARCH(" ",B2,1)-ii) |
| Last Proper noun Issue | Khan |
Separating Names from Text string with "2 Starting time Names"
To extract names with the beginning proper noun having ii names follow the formula stated in the beneath table.
| Full Proper noun | Syed Mufrad Nabi Swapnil |
| Description | 2 Kickoff Names |
| Formula For First Proper name | =LEFT(B2, SEARCH(" ",B2,SEARCH(" ",B2,1)+1)) |
| Start Name Consequence | Syed Mufrad |
| Formula For the Eye name | =MID(B2,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)+1,SEARCH(" ",B2,SEARCH(" ",B2,SEARCH(" ",B2,1)+one)+i)-(SEARCH(" ",B2,SEARCH(" ",B2,1)+1)+1)) |
| Middle Name Result | Nabi |
| Formula for the Last Name | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,SEARCH(" ",B2,one)+i)+1)) |
| Last Name Result | Swapnil |
Separating Names from Text cord with "three Concluding Names"
Use the formula given in the beneath table to extract the showtime and terminal name which has 3 names as the terminal proper name.
| Full Proper name | Syed Waliun Nabi Ony |
| Description | three Last Names |
| Formula For the First Name | =LEFT(B2, SEARCH(" ",B2,1)) |
| Showtime Proper noun Result | Syed |
| Formula For the Concluding Proper noun | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,ane)) |
| Concluding Proper name Result | Waliun Nabi Ony |
Separating Names from Text string with "2 Last Names"
Use the formula given in the beneath tabular array to extract the start and final name which has 2 names as the last name.
| Full Name | Tasfiq Chowdhury Pranto |
| Description | 2 Last Names |
| Formula For the First Name | =LEFT(B2, SEARCH(" ",B2,1)) |
| First Proper noun Outcome | Tasfiq |
| Formula For the Last Name | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,ane)) |
| Last Name Result | Chowdhury Pranto |
Separating names with "Suffix"
Names that contain suffix can exist extracted by using the formulas stated in the below table.
| Total Proper name | King Robert III |
| Description | Suffix |
| Formula For the First Proper noun | =LEFT(B2, SEARCH(" ",B2,1)) |
| First Proper name Upshot | Rex |
| Formula For the Last name | =MID(B2,SEARCH(" ",B2,1)+one,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-(SEARCH(" ",B2,1)+i)) |
| Last Name Outcome | Robert |
| Formula for Suffix | =Correct(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,one)+1)) |
| Suffix Outcome | III |
Separating Names without "Prefix"
To ignore prefix in a name while extracting the Kickoff and last proper name you tin can apply the beneath formulas.
| Total Proper noun | Mr. Ashiqur Rahman |
| Description | Names with Prefix |
| Formula For the Offset Proper name | =MID(B2,SEARCH(" ",B2,1)+ane,SEARCH(" ",B2,SEARCH(" ",B2,1)+1)-(SEARCH(" ",B2,ane)+1)) |
| Start Name Result | Ashiqur |
| Formula For the Last Name | =Correct(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1)) |
| Last Proper noun Issue | Rahman |
Separating names with "Hyphenated last Names"
Some people's names incorporate hyphens. It is hard sometimes to extract the hyphenated names. You tin easily extract the hyphenated concluding proper name by using the formula in the table.
| Total Name | Hasan Uz-Zaman |
| Clarification | Proper name with Hyphenated last name |
| Formula For the Beginning Proper name | =LEFT(B2, SEARCH(" ",B2,one)) |
| First Name Result | Hasan |
| Formula For the Last Name | =RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1)) |
| Last Name Result | Uz-Zaman |
Download The Working File
Conclusions
As nosotros can come across there are a lot of methods to divide words in Excel using formulas. For extracting words in unlike columns Excel has a feature named Text to Column. When you desire to extract names from a whole column to different columns yous can easily use the Text to Column feature but while working on some specific cells yous need to use the formula to extract words.
Hope you will find this article useful. Follow us for more than useful articles.
Further Readings
- Split String by Character in Excel (6 suitable Means)
- Splitting Text in Excel Using Flash Fill
- How to Divide a String into an Assortment in VBA (3 Ways)
How To Separate Words In Excel Using Formula,
Source: https://www.exceldemy.com/how-to-separate-words-in-excel-using-formula/
Posted by: parhamfeciat.blogspot.com
0 Response to "How To Separate Words In Excel Using Formula"
Post a Comment