In some cases, we need to convert or adjust or transform the data cell in a specific format without changing the originality. In this article, I’ll discuss 4 methods of formulas to use cell format formula in Excel.

## What is Cell Formatting in Excel?

Cell formatting is a process of adjusting or changing the appearance without hampering any transformation of the original value of the cell.

## Cell Format Formula in Excel: 4 Suitable Examples

For today’s analysis, we have a dataset where the name of items are provided with their order id, date, states and sales.

### 1. Cell Format Using the TEXT Function

**The TEXT function** converts a value to text in a specific number format. You can visit the dedicated article on **TEXT function** where syntax and usage of the function are discussed elaborately.

However, if you input any value and use the **TEXT function** to convert the value, you’ll see the output like the following picture.

`=TEXT(B5,C5)`

Here, **B5 **is the cell of value and **C5 **is the format-text.

As you see in the explanation on the top-right side of the above picture, the **TEXT function** returns the given value in many formats.

For example, the **TEXT function** returns “1000” from “-1000” and vice-versa.

Besides, it converts the value into a scientific format.

More importantly, the **TEXT function** extracts the name of day, and month from a given date and the function adds leading zeros before any number.

More importantly, we may use the **TEXT function** to find the Sales (product of price and quantity) with the text “The Sales is” in front of the value of the sales.

In such a situation, the formula will be like the following.

`="The Sales is "&TEXT(C5*D5, "$###,###.00")`

Here, **C5 **is the cell for the price of the items, **D5 **is the quantity (Qty), and **$###,###.00 **is the number format for the value of the sales in dollar (**$**) currency.

### 2. Cell Format Using Conditional Formatting in Excel

**Conditional Formatting** is a useful Excel tool that converts the color of cells based on specified conditions. We’ll see some specific and useful applications of conditional formatting.

#### 2.1. Highlighting Any Word in the Dataset

If you guys need to highlight any data for better visualizations, you may use the tool from the **Styles** command bar.

Assuming that you want to highlight the item “TV” and states “Ohio” along with the whole dataset.

For doing that follow the steps below.

⏩ Select the cell range **B5:F15**

⏩ Click on **Home** tab>**Conditional Formatting**>**New Rule**

⏩ Choose the option **Use a formula to determine which cells to format**

⏩ Insert the following formula under the **Format values where this formula is true:**

`=OR(B5="TV",B5="Ohio")`

Here, **B5 **is the cell of AC.

⏩ Lastly, open the **Format** option to specify the highlighting color.

⏩ Press **OK**.

Then the output will look like this.

The above picture reveals clearly that the conditional formatting highlights the word “TV” and “Ohio”.

If you want to change the color of the items that are from “Ohio” states, you can also use the following formula by utilizing the conditional formatting tool.

`=$E5="Ohio"`

Here, **E5 **is the starting cell of the ‘states’ field.

You see that the whole item that belongs to “Ohio” is highlighted with an individual color.

#### 2.2. Highlighting Dates in Next 30 Days

Again if you want to identify and then highlight the dates in the next 30 days from now, use the formula below in the **New Formatting Rule** dialog box.

`=AND(B5>NOW(),B5<=(NOW()+30))`

Here, **B5 **is the starting cell of the order date.

The above formula utilizes the **AND **and **NOW **functions. The **AND function** returns the output into **TRUE** if all conditions are **TRUE** and the **NOW function** returns the current date.

#### 2.3. Highlighting Missing Values

Furthermore, we can show missing values by synchronizing the lists using the conditional formatting tool.

In that case, use the following formula for list 1.

`=COUNTIF($D$5:$D$11, B5)=0`

Here, **D5:D11** is the cell range of List 2, and **B5 **is the starting cell of List 1.

And the formula for list 2 is-

`=COUNTIF($B$5:$B$15, D5)=0`

Here, **B5:B15** is the cell range of list 1 and **D5 **is the starting cell of list 2.

#### 2.4. Applying Multiple Criteria As Cell Format

Lastly, we can apply **AND** logic with multiple criteria in the whole dataset and highlight the output with the conditional formatting tool.

For example, we can highlight the items having a price less than or equal to $300 and sales are greater than or equal to $2000.

For this, put the following formula in the **New Formatting Rule** dialog box.

`=AND($E5<=300,$G5>=2000)`

Here, **E5 **is the starting cell of price and **G5 **is the starting cell of sales.

So, only 4 items have the requirements of having the price less than or equal to $300 and sales are greater than or equal to $2000.

### 3. Cell Format Formula for VBA Coding

Now, let’s see how we can apply the **VBA** Format function to convert the value into a specific format.

**Step 1: **

Firstly, open a module by clicking **Developer**>**Visual** **Basic**>**Insert**>**Module**.

**Step 2: **

Then, copy the following code in your module.

```
Sub Format_func()
Range("C5").Value = Format(Range("B5"), "Currency")
Range("C6").Value = Format(Range("B6"), "Long Date")
Range("C7").Value = Format(Range("B7"), "True/False")
Range("C8").Value = Format(Range("B8"), "Standard")
Range("C9").Value = Format(Range("B9"), "Fixed")
Range("C10").Value = Format(Range("B10"), "Long Time")
Range("C11").Value = Format(Range("B11"), "Short Time")
End Sub
```

**Step 3:**

Finally, run the code.

**Notice:**

The following things are essential in the above **VBA** code.

- Worksheet name: Here, the worksheet name is “
**Format_func**”. - Logic: The Format function returns the value in a specific format.
- Input Cell: Here, the input cell is
**B5**,**B6**, etc. - Output range: The output range is
**C5**,**C6**, etc.

After running the code, the output will be as follows.

### 4. Cell Format Utilizing LEFT, MID, RIGHT, LEN & FIND Function

Right now we’ll see some applications of **LEFT**, **RIGHT**, **MID**, **FIND**, and **LEN **functions.

The **RIGHT function** is designed to return a specified number of characters from the rightmost side of a string. We can use the **RIGHT function** to remove characters from left from “Order ID-Items”.

The formula will be:

`=RIGHT(B5,LEN(B5)-C5)`

Here, **B5 **is the cell of combined “Order ID-Items”, and **C5 **is the “Num chars”.

Now, we have found the items in a text-only cell format.

Moreover, we can also format the cells in numeric format from a combination text-number(“Order ID-Items”).

The formula will be:

`=VALUE(LEFT(B5,(LEN(B5)-C5)))`

Here, **B5 **is the cell of combined “Order ID-Items”, **C5 **is the starting cell of “Chars”.

Furthermore, the **MID function** can be used to extract the last from the ‘Name of orderer’.

The formula will be:

`=MID(C5,SEARCH(" ",C5)+1,LEN(C5)-SEARCH(" ",C5))`

Here, **C5 **is the starting cell of “Name of Orderer”

Last but not least, we can utilize the **FIND function** to get the First name from the Name of the orderer’.

The formula will be:

`=MID(C5,1,FIND(" ",C5)-1)`

Here, **C5 **is the starting cell of “Name of Orderer”.

These are not the conventional ways of formatting cells but can be helpful while extracting to organize in a specific cell format.

**Download Practice Workbook**

## Conclusion

This is how you may adjust cell format using formula in Excel. I firmly believe this article will enhance your capability to do cell formatting in Excel. However, if you have any queries or suggestions, do not forget to share them in the comments section below.

## Related Articles

**<< Go Back to Excel Cell Format | Learn Excel**