Re: Excel VBA Find Column, search text Originally Posted by Rick Rothstein By the way, you do not have to select the range in order to work with the selection. You can use the range directly in the For statement if you want (it is considered better programming practice to do that). In this tutorial, they will take four examples to show how text to column works: Step-by-Step Learn How To Use Text To Column In Excel: Case Study 1: Split the text as last name and first name in two different columns. Case Study 2: Transfer Single Columnar Data into Multiple Columns.
To separate the contents of one Excel cell into separate columns, you can use the 'Convert Text to Columns Wizard'. For example, when you want to separate a list of full names into last and first names.
1. Select the range with full names.
2. On the Data tab, in the Data Tools group, click Text to Columns.
The following dialog box appears.
3. Choose Delimited and click Next.
![Column Column](/uploads/1/2/5/0/125000983/769791023.png)
4. Clear all the check boxes under Delimiters except for the Comma and Space check box.
5. Click Finish.
Note: This example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.
Result:
Function Description
The Excel SEARCH function returns the position of a specified character or sub-string within a supplied text string.
The function is not case-sensitive. If you want to perform a case-sensitive search, use the Excel Find function instead.
The syntax of the Search function is:
SEARCH( search_text, within_text, [start_num] )
Where the function arguments are:
search_text | - | The character or sub-string that you wish to search for. |
within_text | - | The text string that is to be searched. |
[start_num] | - | An optional argument that specifies the position in the within_text string, from which the search should begin. If omitted, this takes on the default value of 1 (i.e. begin the search at the start of the within_text string) |
Wildcards
You can also use the following wildcards in the search_text argument:
? - matches any single character
* - matches any sequence of characters
e.g. the condition 'A*e' will match any substring beginning with 'A' and ending in 'e'.* - matches any sequence of characters
If the supplied search_text is found, the Search function returns a number representing its position in the within_text. If the supplied search_text is not found, the function returns the Excel #VALUE! error.
![Column Column](https://support.content.office.net/en-us/media/634143ac-5562-4c20-ad45-f5b2321ba989.jpg)
Search Function Examples
Column B of the following spreadsheet shows examples of the Excel Search function, used to search for various characters within the text string 'Original Text'.
Formulas:
| Results:
|
Note that, in the above examples:
- As the Search function is not case sensitive, the upper- and lower-case search_text values, 'T' and 't', return the same result (see cells B1 & B2).
- In the example in cell B4, the [start_num] argument is set to 4. Therefore the search begins at the fourth character of the within_text string and so returns the second occurrence of the character 'i'.
See the Microsoft Office website for further details and examples of the Excel Search function.
Search Function Error
If you get an error from the Excel Search function this is likely to be the #VALUE! error:
#VALUE! | - | Occurs if either:
|
Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions
Return to the List of All Built-In Excel Functions