Select Just One Cell in the Paste Area or an Area Thats the Same Size
Yesterday, i of my colleagues...
...who is really passionate to learn Excel these days...
...asked me this:
Do you know how to remove the first character from a prison cell in Excel?
I told him a small formula (which was easy) for that, but he was not satisfied and want another method.
And after that, I told him about five dissimilar methods for this simply then he got confused most which method is piece of cake.
Even I have created a small UDF to make information technology super easy for him to remove n number of characters from the start of a string.
And today in this post, I'd like to share with yous all these methods which I've found easy and user-friendly to remove a graphic symbol from starting off a string.
…here we go.
i. Combine RIGHT and LEN to Remove the Beginning Character from the Value
Using a combination of Correct and LEN is the most suitable way to remove the start graphic symbol from a cell or from a text string.
This formula simply skips the first grapheme from the text provided and returns the rest of the characters.
In the beneath example, we have a list of roll numbers where we have alphabet "H" equally a prefix.
Here we need to remove "H" and return rest of the string and the combination of RIGHT with LEN can help usa.
Here'south what you demand to practise, select the cell B2 and enter the below formula into it and later on that drag that formula to the cease.
In the result, information technology has returned the last three characters by removing the get-go character from the actual text cord.
just how this formula works
First of all, we have used LEN office which counts the total number of character from the prison cell and returns that number.
After that, we take used the RIGHT role to get the characters from the same cell starting from the correct.
As you know, y'all demand to specify the number of characters you want to extract using the Correct function, and for this, nosotros accept used the number returned by the LEN…
…only, instead of returning all the character nosotros take subtracted ane from it so that the formula skips the last graphic symbol from the correct which is the showtime character from the left.
If you want to remove more that i graphic symbol so simply replace i in the right role with 2, 3, 4 for whatever the characters you want to remove.
two. Using Replace Function to Remove Start Character from a Cell
And if you want to utilize a single formula, you tin use REPLACE function.
In below example with the same list of roll numbers, we have used REPLACE to remove the first grapheme from the cell.
It simply replaces the commencement graphic symbol with an empty character using the below formula.
= REPLACE (A2,1,1,"")
how it works
With REPLACE function, you lot can supervene upon a character or more than one graphic symbol using the starting position of the graphic symbol(s).
Hither in this formula, we have referred to the chief text cord and specify the starting position i (from the characters should exist replaced) and and so mentioned the number of characters to replace.
As we have specified the starting position one and only 1 character to replace and skip specifying the character to replace with.
It has removed the first graphic symbol and but the first 1 without replacing it with any other character. That's why what we got in return is the text string without the kickoff character.
In that location is no such departure between this method and the method which we have used above.
If y'all desire to remove more that one character then only supplant 1 (characters to supplant) with 2, 3, 4 for whatever the characters you lot want to remove.
three. Combine MID and LEN to Become Values afterward Removing Starting time Character
The tertiary formula which we can use is a combination is of MID and LEN. sThis combination works just similar the ii methods which we accept used in a higher place.
In below example, we have used this combination to get characters from a cell except for the beginning about character. And the formula is:
how this formula calculates
In this formula, LEN returns the total number of characters in the cord.
As nosotros want to remove the starting time character from the original text string we need to deduct one from the number returned by the LEN.
And then when LEN returns 4 as the total number of characters we deduct ane from it.
After that in the MID, we have referred to the original text string and mentioned the starting position as two so that information technology skips the kickoff graphic symbol.
In the effect, the formula has returned the rest of the characters except the first.
4. Remove First Character with Text to Cavalcade from a Jail cell
If instead of removing the first character if you want to separate it from the original text cord then you lot can utilise TEXT to COLUMN method.
By using TEXT to Cavalcade yous can split up the first character and rest of the characters into two different columns instead of removing it.
Beneath are the simple steps you lot demand to follow here:
Finally, hither y'all have 2 columns, 1 with the commencement character and other with the rest of the characters.
You tin can delete if you lot don't want to use the column with start character.
In that location'due south one thing you demand to understand that this is not a dynamic method to remove a character and if yous want to separate more than than one grapheme and then you only need to adjust the vertical line up to that grapheme.
five. Applying Flash Fill to Remove First Grapheme using a SHORTCUT Fundamental
On a quick note, you can also use flash fill to remove the first character from a text string.
Let me explain to you how to use it and I'm using the aforementioned example data hither which I have used in other methods.
- First of all, in the jail cell A1 enter the outset text past skipping the get-go character from the original text.
- After that, hitting enter and come down to the 2nd cell in the column.
- At present, press the keyboard shortcut Command + E to employ a flash fill to apply.
how it works
The moment you press the shortcut key flash fill follows the pattern which you have used in the kickoff entry.
In simple words, when you entered the text in the offset jail cell you skipped the showtime character from the text…
…and wink fill followed the same design return text in the rest of the cells without the starting time character
six. UDF to Remove Start (n) Graphic symbol(s) from a Cell
To give you more ease I accept created a User Defined Role.
It will exactly do the aforementioned which nosotros can do what other formulas do. But copy the post-obit lawmaking and use the steps to insert in your workbook.
- First of all, open up the visual basic editor from your workbook using Alt + F11.
- After that, insert a module and paste higher up code into information technology.
- Now, come up back to your worksheet and insert the following formula.
Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
Cease Office
how this UDF works
In this UDF, I have simply used the formula which nosotros have practical in the get-go method.
Y'all only need to refer to the cell where you have the text or you can directly enter the text into the function so you need to enter a number to specify the character you want to remove.
Yes, that's it.
Notation: If you want to remove more than 1 character from the starting of the string then you lot can enter that number in the function.
Sample File
Not all the time y'all'll have the beginning character an actual character. Sometimes it can exist an unwanted space . In that situation, you can remove it using the TRIM function.
TRIM removes unwanted characters from the starting and from the catastrophe of a text cord.
=TRIM(" Puneet")
In the end
In conclusion, I'd similar to say that all the above methods are quite easy and user-friendly to use.
Offset three methods are based on formulas which are dynamic and on the other manus text-to-column is quick but y'all need to use once more and once more so sometimes it comes less handy.
But the important matter is if you want to remove more than one graphic symbol (2, three, 4 etc) from the string/prison cell so using a UDF is a perfect way.
If you ask me method one and v is my favorite. But here you lot need to tell me i thing.
Which method practice you like the most?
Please share your views with me in the comment section, I'd love to hear from you and please don't forget to share this tip with your friends.
You must read these as:
Near the Author
Puneet is using Excel since his higher days. He helped thousands of people to sympathise the ability of the spreadsheets and learn Microsoft Excel. You tin can find him online, tweeting nearly Excel, on a running runway, or sometimes hiking up a mount.
Source: https://excelchamps.com/blog/remove-first-character/
Post a Comment for "Select Just One Cell in the Paste Area or an Area Thats the Same Size"