Extract text from a cell in Excel
You can use one of the text manipulation formulas available in Excel. These include LEFT() and RIGHT(). You may also need to use the LEN() formula, which calculates the length of a value in a cell.
In this case, let's assume that the value you are working with, wilson12345 is in cell B2.
Use the RIGHT() formula in another cell (say, C2) as follows:
=RIGHT(b2,5)
This will extract the five characters from the right end of cell B2 and place them in B3. Because they are all digits, Excel will automatically treat them as a number in B3. In a table of data where you used this formula in column C for every row, you could then sort the data in the table based on the numbers this formula generated in column C.
Let's look at another way to manipulate this formula. What if you actually wanted to extract the text, smith, from the cell instead? You would use the LEFT() formula in C2 instead:
=LEFT(b2,6)
This will extract the six characters starting from the left end of the value in B2 and place it in C2. This gives you an introduction to text manipulation in Excel.
You have one or more cells containing text or a combination of text and numbers. You want a fast way to extract part of the value in each cell without manually editing each cell.
You have a cell that contains the following value smith12345 You want to extract the 5 digit number from the cell into another cell.
- Software:
- Level of difficulty:
About Us
Fiveminutelessons.com is a website dedicated to providing free training on a range of subjects. We're starting with lessons on Microsoft Word, Excel and PowerPoint but have plans to add other subjects over time such as Adobe Photoshop and Microsoft Project as well as non-software subjects.
We're based in Tauranga, New Zealand but have years of experience in the UK, USA and Ireland working in the training industry with a range of clients from small businesses to blue chip corporates.
Commercial Services
As well as the free training you get at www.fiveminutelessons.com, we also offer a range of commercial services to help you with your training and online learning requirements including:
- Private labelling
- Courseware development
- e-learning development
- Corporate subscriptions
- Learning consultancy services
- Documentation and user guide development
- Development of innovative corporate learning environments
We offer these services for Microsoft Word, Excel and PowerPoint, as well as a range of other popular software applications. We can also assist with documentation and courseware development for custom or in-house software packages.
Contact us now to find out more about our learning consultancy and courseware development services