Select part of a cell

[Joe]

I have three words per cell, and a whole list of cell.
I want to pick up the last word in the cell only and put
it in another cell.
Each cell/word has a diffent number of charachers so i
cant use the MID function
does anybody have any ideas
Thanks

 

[Don Guillett]

You could use data>text to columns>delimited>space or
A formula solution
=MID(A1,FIND("$",SUBSTITUTE(A1," ", "$", LEN(A1) - LEN(SUBSTITUTE(A1," ",
""))))+1, 255)

 

[Joe]

Hello
how do you do data>text to columns>delimited>space?

Thanks

 

[Ken Wright]

Each cell/word has a diffent number of charachers so i

cant use the MID function

Remember, MID expects you to specify where to start pulling the string you
want from the string you have, so think about it - How do you tell it to
find the last space in the string and then start from the next character.
FIND() is good because it will find a space in your string for you, but the
problem is you have more than one space, so what you can do is somehow
switch the very last space for a character that is likely to be unique to
your string, eg something like [. A good function to do this would perhaps
be SUBSTITUTE which works similar to FIND but allows you to replace a
specific instance of a character. Thing is you then need to know how many
instances of that character there are in the string, so maybe substitute all
instances of a space with nothing, and then look at the length of the string
before and after.

So from that assumption, and working with 'your_string' in A1 (I'll assume
its 'abc def ghijk'), lets first see how many spaces are in your string:-

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) < This gives 2 in my example string
OK so far, so now lets switch the 2nd instance of a space (Which is the
character just before the name you want to get) for a unique character such
as [

=SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) < This now
gives me 'abc def[ghijk' >

So now we need to find where that character [ actually is so that we can use
that for the MID function, so:-

=FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) <
This gives me 8 >

So now I know that the last space in the original string is sat in the 8th
position, and i know that the data i want starts from that plus one, ie the
9th, so....

=MID(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,LEN(A1))

 

[RagDyeR]

Select the cells in the column that you wish to "separate", then:

<Data> <TextToColumns>

Make sure that "Delimited" is checked, then <Next>,

Then click in "Space", and you'll immediately see in the DataPreview window,
just how your data will be separated.

Click <Next> again, and in this window you can decide on exactly what to do
with the data.

First of all, in the "Destination" box, you see the default *start* location
for the separation.
This is the actual column that *now* contains your data, meaning that your
original data will be *replaced* with the first column of separated data.
If you wish to retain the original datalist, you can change the destination
of the separated data to any column or row you wish.

Next, you can pick and choose to which of the separated columns you actually
wish to "save and/or move".

Let's say in your case, with the original data in Column A, you only want
the third column of separated data to be printed into Column D.
Click in the first column of the DataPrieview window, selecting it (black),
then click on "Do Not Import Column".
You'll see the column header change to "Skip Column".
Then click in (select) the second column, and *again* click on "Do Not
Import Column", skipping that one also.
Change the address in the "Destination Box" to D1, and finally click on
<Finish>.

One of the advantages of using this feature is, that when you're finished,
you have true data, and *not formulas* that are only returning data, where
you might have to go through additional steps and procedures to eliminate
the formulas to leave the data behind.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hello
how do you do data>text to columns>delimited>space?

Thanks

 

[RagDyeR]

Showoff !<g>

Very impressive though.<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Each cell/word has a diffent number of charachers so i
cant use the MID function

Remember, MID expects you to specify where to start pulling the string you
want from the string you have, so think about it - How do you tell it to
find the last space in the string and then start from the next character.
FIND() is good because it will find a space in your string for you, but the
problem is you have more than one space, so what you can do is somehow
switch the very last space for a character that is likely to be unique to
your string, eg something like [. A good function to do this would perhaps
be SUBSTITUTE which works similar to FIND but allows you to replace a
specific instance of a character. Thing is you then need to know how many
instances of that character there are in the string, so maybe substitute all
instances of a space with nothing, and then look at the length of the string
before and after.

So from that assumption, and working with 'your_string' in A1 (I'll assume
its 'abc def ghijk'), lets first see how many spaces are in your string:-

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) < This gives 2 in my example string
OK so far, so now lets switch the 2nd instance of a space (Which is the
character just before the name you want to get) for a unique character such
as [

=SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) < This now
gives me 'abc def[ghijk' >

So now we need to find where that character [ actually is so that we can use
that for the MID function, so:-

=FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) <
This gives me 8 >

So now I know that the last space in the original string is sat in the 8th
position, and i know that the data i want starts from that plus one, ie the
9th, so....

=MID(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,LEN(A1))

 

[Ken Wright]

LOL - A few days off, with plenty of time on my hands - Kids are happy +
Wife is happy = I'm chilled out - Quite happy to teach how to fish rather
than throw fish, or at least for a day or two anyway.