Whether you want to display the first x characters from the identification card or a zip code there are a few ways you can achieve this.
Get the first x characters
In this case, we deal with an identification number that always starts with three letters and six numbers after that.
To get the first three letters use this formula.
|
1 |
=LEFT(A2,3) |
For the numbers, we are going to use the RIGHT function.
|
1 |
=RIGHT(A2,6) |
Get the first x characters before a character
Now, we are going to deal with a more complicated problem. The example below shows numbers separated by dashes.
We want to get the first part of the number. We can’t get the first x numbers because the number of characters before the dash is not constant. But we can modify the formula to do the job.
|
1 |
=LEFT(A2,FIND("-",A2)-1) |
The FIND method returns the position of the first dash minus 1.
Get the first x characters after a character
In the previous example, we could see how to get the first characters before a specific character. Now we are going to get a fixed number of characters after a character.
|
1 |
=RIGHT(A2,LEN(A2) - FIND("-",A2)) |
The LEN function gets the number of characters minus the first occurrence of the dash.
11 – 4 = 7.
In other words, the formula gets 7 characters from the RIGHT.