| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 79326 | 2007-05-16 10:29:00 | Excel Query | beeswax34 (63) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 550278 | 2007-05-16 10:29:00 | In a excel database I have a long list of words in a column. For eg: PC World is great PC World is fantastic PC World is great PC World is great PC World is fantastic PC World is fantastic PCWorldisfantastic Now I need to just take the words "PC World is" and put them in a column straight to the column with all the statements. Now I have a formula that allows me to do that for nearly all statements except the one without any spaces in it. How do I tell Excel, that if there isnt a space at all in that cell, to pick everything in that cell and use that. So while most of my new cells would have 'PC World is", a couple of cells should end up with "PCWorldisfantastic" Thanks:help: |
beeswax34 (63) | ||
| 550279 | 2007-05-16 23:34:00 | Assuming your list starts at A1 then the following formula in B1 will work for the specific data you have supplied. =IF(FIND("is",A1)=10,LEFT(A1,11),A1) |
rad_s4 (7401) | ||
| 550280 | 2007-05-19 09:27:00 | Hi Assuming not every line starts with either "PC World is " or "PCWorldis" then the following formula will detect if there are any spaces or not: =IF(ISERROR(FIND(" ", A1)),A1,...{your existing formula}...) HTH, Andrew |
andrew93 (249) | ||
| 550281 | 2007-05-19 13:01:00 | Hi, thanks andrew but rad's formula worked just fine. Forgot to mention it here. Thanks for your help again. | beeswax34 (63) | ||
| 1 | |||||