How to split a string and add a decimal point separator to numerical data in Excel
For every accountant, numbers are extremely important because even just missing or mistyping one digit in a financial report can cause a lot of difficulties for the entire company. Especially for long numerical strings (such as the company’s revenue), what you need to do is to split the data and add a decimal point separator to the long numerical strings, and then condense them for convenience in tracking and presenting. Read the following Buffcom.net article to learn how.
For example, let’s say we have a numerical string of 11223344 and we need to split the string and add a decimal point to the numerical data so that it appears as 1122.33. To do this, we will need to use a combination of two functions: LEFT, MID, and the & operator. The syntax of these functions is as follows:
1 LEFT function structure
Function syntax: =LEFT(text; [num_chars])
Where:
- Text: a required argument, which is the text string that contains the characters you want to extract.
- Num_chars: an optional argument, which is the number of characters that you want the LEFT function to extract.
2 MID function structure
Function syntax: =LEFT(text; start_num; num_chars)
Where:
- Text: a required argument, which is the text string that contains the characters you want to extract.
- Start_num: a required argument, which is the first character that you want the MID function to extract.
- Num_chars: a required argument, which is the number of characters that you want the MID function to extract.
3 How to split a string by adding a character
First, enter the character string “11223344” into cell A1. Then, in cell A2, enter the following formula:
=LEFT(A1;4)&”.”&MID(A1;5;2)
This formula means that first we use the LEFT function to extract the first 4 characters in cell A1, which is “1122”. Next, we use the & operator to insert a period after “1122”. Finally, we use the MID function to extract the 2 characters starting from the 5th character in cell A1, which is “33”. With just these steps, we have obtained the numerical data in the required format of 1122.33.
How to split a string and add a decimal point separator to numerical data in Excel
Therefore, the article above has instructed you on how to use the LEFT, MID, and & functions to split a string and add characters in Excel. Hopefully, this article will be helpful to you in your work. Good luck!
