separate columns (three letter code)

  • Thread starter Thread starter mariekek5
  • Start date Start date
M

mariekek5

Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
 
--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish
 
Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

Jacob Skaria said:
--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

--
Jacob


mariekek5 said:
Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
Click to expand...
Click to expand...
 
Hi,

maybe this

=LEFT(RIGHT(A1,4),3)
--
Mike

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


mariekek5 said:
Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

Jacob Skaria said:
--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

--
Jacob


mariekek5 said:
Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Use RIGHT()

=RIGHT(A1,3)

--
Jacob


mariekek5 said:
Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

Jacob Skaria said:
--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

--
Jacob


mariekek5 said:
Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Great, this works perfectly.

Is it also possible to get the rest in another column? Like text minus
RIGHT(A1,3)...?



Jacob Skaria said:
Use RIGHT()

=RIGHT(A1,3)

--
Jacob


mariekek5 said:
Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

Jacob Skaria said:
--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

--
Jacob


:

Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Yes; try

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

--
Jacob


mariekek5 said:
Great, this works perfectly.

Is it also possible to get the rest in another column? Like text minus
RIGHT(A1,3)...?



Jacob Skaria said:
Use RIGHT()

=RIGHT(A1,3)

--
Jacob


mariekek5 said:
Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

:

--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

--
Jacob


:

Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Great, works perfectly!

Thank you!

Jacob Skaria said:
Yes; try

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

--
Jacob


mariekek5 said:
Great, this works perfectly.

Is it also possible to get the rest in another column? Like text minus
RIGHT(A1,3)...?



Jacob Skaria said:
Use RIGHT()

=RIGHT(A1,3)

--
Jacob


:

Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

:

--Select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

--
Jacob


:

Hi, does someone know how to separate text from one column into two columns?

I have columns like
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top