duplicate last 4 characters of a cell

**Christopherdj** · 12-21-2011, 02:15 AM

I would like to reproduce the contents of a cell but only the last 4 characters.
Raw Data is in column A, and contains text and numbers but I'd like to sort the data using the last 4 characters in the cell.
Each cell in Column A has varying amounts of text and numbers, so any formula needs to look right in cell contents, and only result the last 4 characters of that cell.

So, I'd like to look at Cell A3, then reproduce the last 4 characters in Cell A7.

Is this possible?

**MarvinP** · 12-21-2011, 02:34 AM

Hi,

I think that a formula like =RIGHT(A1,4) will be what you are looking for.

In your case above, in A3 =RIGHT(A7,4) should work.

**Christopherdj** · 12-21-2011, 05:43 PM

Thanks for the solution - works fine, but has identified another problem.

It appears my database has placed a space as the last character in some instances, and yes I could produce the last 5 characters as per your formula. But having spaces won't allow me to sort correctly.

My solution to this is to copy the entire column, then use "paste special" selecting "values", then I use "search and replace" looking for a "space" and replace with "" nothing.
This now allows me to sort the data.

**scottylad2** · 12-21-2011, 05:56 PM

just wrap the formula in the =TRIM(function

**Christopherdj** · 12-21-2011, 06:13 PM

Thanks Scottylad2 - I was totally unaware (like most advanced functions) this was possible. Anything that saves me time is very much appreciated.

**scottylad2** · 12-21-2011, 06:53 PM

THe Trim will remove a trailing or preceding space from your data, if you were referencing a text string it wouldn't interfere with spaces between word, only the ones before or after your data. Stick around this site, some verrrry smart Excellers on here who are only too happy to assist.

You'll be hooked in no time

**Christopherdj** · 12-21-2011, 07:25 PM

I already am hooked. I have grown so much in knowledge in the past couple of months with excel and formulas etc. and it is all thanks to this site and in particular all those contributors to solutions. This is a fantastic resource and the ones supplying solutions should be rewarded with their weight in gold........ they are brilliant.