0

I have a column of data that is alpha-numeric and has multiple "." separator between various numbers, what I am looking to do is pull the last five characters of this string and move it to a separate column. Is there a feature(or combination of features) that does this in excel.

As an example, my data looks like:

123456.01.02.03.04.0510001 123456.01.02.03.04.0510B01 123456.01.02.03.04.0510D01 123456.01.02.03.04.0510007

What I am looking for is to truncate the last five characters (10001, 10B01, 10D01, 10007) and then move them to a new column.

I have never played with macro's in excel, is this something a macro can do or is there a simpler solutions.

Thanks

user3352908
  • 1

1 Answers1

0

Have you considered using the following family of search/parsing functions?:

LEFT(text,num_chars)
MID(text,start_num, num_chars)
RIGHT(text, num_chars)
SEARCH(find_str,within_str, start_num)
and LEN(text)

With these I would think you could isolate and pull out just about any combination of text segments you need to.

For example:
Say cell a1 has contents: "123456.01.02.03.04.0510007"

In cell b1 you could place "=RIGHT(a1, 5)"

This should result in 10007 in cell b1

Like this:
Enter data and formula:
enter image description here

And results are:
enter image description here

ryyker
  • 22,849
  • 3
  • 43
  • 87
  • Thanks ryyker !!! The Right command will work, since pulling the last five characters into a separate column was what I was looking for (I need to be able to sort based on that value). Truncating these five characters from the a1 cell was not as important. Thanks for the quick response. – user3352908 Feb 25 '14 at 21:29