Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to check if first character in a cell is a letter or number in Excel?
Excel has a bunch of formulas and data analysts use this application to prepare various reports and dashboards. To support that analysis this article will help an excel user to identify all the cells whose values either starts with a number or an alphabet from a collection of dataset. We will identify these cells using the combination of following formulas.
IF function
ISERR function
Left function
Check if first character in a cell is a number or a letter
The following formulas can be used to check if the first character of a cell is number or a letter.
=IF(ISERR(LEFT(cell_location,1)*1),"Letter”, “Number")
In this formula, we are calculating the value of a cell’s left most character multiplied by 1. If the obtained value is not a number then it will return Letter else Number.
=IF(ISNUMBER(VALUE(LEFT(cell_location, 1))),"Number", "Letter")
In this formula, we are calculating the value of left most character of a cell. If the returned value is a number, then it will return TRUE (by default) or as per the defined value (Number in our case).
Step 1 − A sample worksheet has been shown below with some sample data.
Step 2 − Under the column C, the following formula has been used to get the desired result. Enter the formula in a cell and press enter.
=IF(ISERR(LEFT(B2,1)*1),"Letter”, “Number")
Step 3 − Now drag the cell to copy the formula in other cells also to view the output against all cells. Enter the formula in a cell and press enter.
Step 4 − Under the Column D, the following formula has been used to get the desired result.
=IF(ISNUMBER(VALUE(LEFT(B2, 1))),"Number", "Letter")
Step 5 − Now drag the cell to copy the formula in other cells also to view the output against all cells
Formula Syntax Description
| Argument | Description |
|---|---|
| IF(logical_test, {value_if_true},{value_if_false} |
|
| LEFT(text, [num_chars]) |
|
| ISERR(value) |
|
Conclusion
The start or end character of a cell value can be identified using many other combinations of excel functions. However, these the most used functions for the same. The same functions can be used to identify the cell ending with a number or letter by replacing LEFT from RIGHT.
=IF(ISERR(RIGHT(cell_location,1)*1),"Letter”, “Number")
=IF (ISNUMBER(VALUE(RIGHT(cell_location, 1))),"Number", "Letter")
13K+ Views
