separate letters from cell
 

Hi, can someone help me with the following:

In 400 cells I have a name + a code with three letters, for example: Mister
Blackwell RRT. I would like 'Mister Blackwell' in one cell, and 'RRT' in
another cell. Is it possible to separate the three letter code from the name?

Thanks a lot in advance.

Marieke

separate letters from cell
 

Hi,

to get the name

=TRIM(LEFT(A1,LEN(A1)-3))

To get the 3 letter code

=RIGHT(A1,3)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"mariekek5" wrote:

Hi, can someone help me with the following:

In 400 cells I have a name + a code with three letters, for example: Mister
Blackwell RRT. I would like 'Mister Blackwell' in one cell, and 'RRT' in
another cell. Is it possible to separate the three letter code from the name?

Thanks a lot in advance.

Marieke

separate letters from cell
 

If your "Mister Blackwell RRT" is in A1:
To get the name: =LEFT(A1,LEN(A1)-4)
To get the three letter code: =RIGHT(A1,3)

These are, of course, formula so you must retain the original
Learn about Copy followed by Paste Special /Values to transform formula to
data.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mariekek5" wrote in message
...
Hi, can someone help me with the following:

In 400 cells I have a name + a code with name + a code with three letters,
for example: Mister
Blackwell RRT. I would like 'Mister Blackwell' in one cell, and 'RRT' in
another cell. Is it possible to separate the three letter code from the
name?

Thanks a lot in advance.

Marieke

separate letters from cell
 

If its always 3 letters at the rightmost bit in the string
Your data in A1 down
In B1: =RIGHT(A1,3)
In C1: =TRIM(SUBSTITUTE(A1,B1,""))
Copy B1:C1 down. Col B returns the 3 letters, col C returns the rest of the
string minus the 3 letters. joy? wave it, hit the YES below
--
Max
Singapore
---
"mariekek5" wrote:
In 400 cells I have a name + a code with three letters, for example: Mister
Blackwell RRT. I would like 'Mister Blackwell' in one cell, and 'RRT' in
another cell. Is it possible to separate the three letter code from the name?

separate letters from cell
 

Thanks a lot Mike en Max!

"mariekek5" wrote:

Hi, can someone help me with the following:

In 400 cells I have a name + a code with three letters, for example: Mister
Blackwell RRT. I would like 'Mister Blackwell' in one cell, and 'RRT' in
another cell. Is it possible to separate the three letter code from the name?

Thanks a lot in advance.

Marieke