Description

get certain text from excel cell ?

### How to Extract Specific Text from an Excel Cell: A Comprehensive Guide

Working with extensive datasets in Excel often requires extracting specific information from a cell. Whether you need to pull data before a certain character, after a specific space, or between two delimiters, Excel offers multiple methods to accomplish this. In this guide, we will explore several techniques to extract text from an Excel cell, including formulas and VBA programming.

#### 1. Using the LEFT Function

The `LEFT` function is one of the simplest ways to pull out text from the leftmost side of a cell string. For instance, if you have text in cell B5 and you want to extract the first 4 characters:

1. Type the formula `=LEFT(B5,4)` in cell C5.
2. Press `Enter`. The first four characters of the text in B5 will now appear in C5.
3. Drag the fill handle from C5 down to C9 to apply the formula to other cells.

#### 2. Combining LEFT and FIND Functions

For extracting text before a specific character, a combination of `LEFT` and `FIND` functions can be very helpful. Here is how:

1. Use the formula `=LEFT(B5, FIND(“-“, B5) – 1)` where ‘-‘ is the delimiter.
2. This formula will return all characters to the left of the ‘-‘.

#### 3. Using VBA to Extract Text

For more complex text extraction tasks, VBA (Visual Basic for Applications) can be used to write macro codes. Here’s a basic example of using `VBA` to extract text:

– Open your Excel workbook and press `ALT + F11` to open the VBA editor.
– Insert a new module by selecting `Insert > Module`.
– Paste the following code:

“`vba
Sub VBA_to_extract_text()
‘Declare variable
Dim Selection_Rng As Range
Dim cell_Value As Range

‘Define Selection range to get from selection
Set Selection_Rng = Application.Selection

‘Apply for loop
For Each cell_Value In Selection_Rng
‘Formula to extract text after space
cell_Value.Offset(0, 1).Value = Right(cell_Value, Len(cell_Value) – InStr(cell_Value.Value, ” “))
Next cell_Value
End Sub
“`

– Select the cells you wish to extract text from.
– Run the Macro.

#### 4. Extracting Text Between Two Commas

If you need to extract text between two commas, the `MID`, `FIND`, and `LEN` functions can be used together.

Here is an example formula to extract text between the first and the second comma:

`=MID(B5, FIND(“,”, B5) + 1, FIND(“,”, B5, FIND(“,”, B5, 1) + 1) – FIND(“,”, B5) – 1)`

#### 5. Using TEXTJOIN, TRIM, and SEARCH Functions

To extract text after the last space in a cell:

`=TRIM(RIGHT(SUBSTITUTE(B5, ” “, REPT(” “, 99)), 99))`

This formula replaces every space in B5 with a series of spaces, and then extracts the last set of spaces (which corresponds to the text after the last space).

#### Conclusion

From the simple `LEFT` function to advanced VBA coding, Excel provides a variety of ways to extract specific text from a cell. Whether you are new to Excel or experienced, these methods offer a valuable skill set to handle and manipulate data efficiently.

Links to Further Resources:
– [ExcelDemy: Extract Text from a Cell in Excel](https://www.exceldemy.com/extract-text-from-a-cell-in-excel/)
– [ExtendOffice: How to Extract Part of Text String from Cell](https://www.extendoffice.com/documents/excel/3639-excel-extract-part-of-string.html)
– [TimeAtlas: How to Extract Substring Text in Excel](https://www.timeatlas.com/extract-excel-substring-text)

By mastering these techniques, you can significantly enhance your data manipulation skills in Excel, allowing you to streamline your work and make data analysis a breeze.

Happy Exceling!