In Excel, how do I extract last four letters of a ten letter string?

excel extract excel-formula
Nitesh Singh picture Nitesh Singh · Jul 12, 2012 · Viewed 249.9k times · Source

For example, if I have

ABS YUR YUAO   
HFH IWO OQNX  
YQO PQM QUCC

How do I extract the last four letters in another column?

Answer

ApplePie picture ApplePie · Jul 12, 2012

No need to use a macro. Supposing your first string is in A1.

=RIGHT(A1, 4)

Drag this down and you will get your four last characters.

Edit: To be sure, if you ever have sequences like 'ABC DEF' and want the last four LETTERS and not CHARACTERS you might want to use trimspaces()

=RIGHT(TRIMSPACES(A1), 4)

Edit: As per brettdj's suggestion, you may want to check that your string is actually 4-character long or more:

=IF(TRIMSPACES(A1)>=4, RIGHT(TRIMSPACES(A1), 4), TRIMSPACES(A1))

Related questions

Excel - extracting data based on another list

I have an Excel worksheet with two columns (name/ID) and then another list that is a subset of the names only from the larger aforementioned list. I want to go through the subset list and then pull the data …

Read More
Replacing one character with another in a string

I have a data like below: A:B:C:D and I want to replace the C with data (say, Z) so that it may look like A:B:Z:D How can I do it?

Read More
Extract number between brackets in Excel?

I've been searching up and down for an answer to this, but haven't found anything I can get to work. It may well be my lack of knowledge in the more "codey" aspects of Excel. Anyway, this is my situation: …

Read More