Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Select part of a cell
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 |
#2
|
|||
|
|||
You could use datatext to columnsdelimitedspace or
A formula solution =MID(A1,FIND("$",SUBSTITUTE(A1," ", "$", LEN(A1) - LEN(SUBSTITUTE(A1," ", ""))))+1, 255) -- Don Guillett SalesAid Software "Joe" wrote in message ... 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 |
#3
|
|||
|
|||
Hello
how do you do datatext to columnsdelimitedspace? Thanks -----Original Message----- You could use datatext to columnsdelimitedspace or A formula solution =MID(A1,FIND("$",SUBSTITUTE(A1," ", "$", LEN(A1) - LEN (SUBSTITUTE(A1," ", ""))))+1, 255) -- Don Guillett SalesAid Software "Joe" wrote in message ... 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 . |
#4
|
|||
|
|||
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)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Joe" wrote in message ... 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 |
#5
|
|||
|
|||
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! ============================================== "Joe" wrote in message ... Hello how do you do datatext to columnsdelimitedspace? Thanks -----Original Message----- You could use datatext to columnsdelimitedspace or A formula solution =MID(A1,FIND("$",SUBSTITUTE(A1," ", "$", LEN(A1) - LEN (SUBSTITUTE(A1," ", ""))))+1, 255) -- Don Guillett SalesAid Software "Joe" wrote in message ... 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 . |
#6
|
|||
|
|||
Showoff !<g
Very impressive though.<g -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Ken Wright" wrote in message ... 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)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Joe" wrote in message ... 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 |
#7
|
|||
|
|||
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please Help...How can I do this? Select a cell | Excel Worksheet Functions | |||
worksheet tab name as part of a cell reference | Excel Worksheet Functions | |||
How do I test a cell for part of text | Excel Worksheet Functions | |||
vlookup to extract part cell content | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |