+ Reply to Thread
Results 1 to 13 of 13

Extracting the first word form every cell in a column

  1. #1
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Extracting the first word form every cell in a column

    I have a a long list of information containing a varying number of words. I need to take the first word in each cell and move it one cell to the left. I have been using the below macro:


    Please Login or Register  to view this content.

    The problem I have with the above is that if there is only one word and therefore no space for the code to find it spits out an error.

    Could I add in an IF statement to say: (IF there is no " " then move data left)? If so how should I write this in?

    Thanks in advance.
    Last edited by mikeymeteora; 01-28-2020 at 08:40 AM.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,781

    Re: Extracting the first word form every cell in a column

    Hi,

    Give this a try

    Please Login or Register  to view this content.
    Cheers!
    Deep Dave

    www.NeedForExcel.com

  3. #3
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Extracting the first word form every cell in a column

    Thanks for this but I am getting an error on "Cells(Counter, 1) = Temp(0)" not sure what I've done wrong

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,392

    Re: Extracting the first word form every cell in a column

    Try
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: Extracting the first word form every cell in a column

    Yet one more...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,781

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by mikeymeteora View Post
    Thanks for this but I am getting an error on "Cells(Counter, 1) = Temp(0)" not sure what I've done wrong
    Are the words separated by Spaces?

  7. #7
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by jindon View Post
    Try
    Please Login or Register  to view this content.
    Thank you, this worked perfectly

  8. #8
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by AlphaFrog View Post
    Yet one more...

    Please Login or Register  to view this content.
    Thank you for this also. Worked slightly differently (didn't move the remaining data out of the cell as in my original, although that wasn't part of my ask so fair enough.) so I'm going to save this for a future problem I will undoubtedly run into.

  9. #9
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by NeedForExcel View Post
    Are the words separated by Spaces?
    The words in the data set are seperated by spaces yes, although there are no spaces in any cells containing only 1 word.

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,781

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by mikeymeteora View Post
    The words in the data set are seperated by spaces yes, although there are no spaces in any cells containing only 1 word.
    I have handled that and tested that aswell.. Well it works for me, not sure why it does not at your end .. I can only say after seeing the error.. Anyways now that you have a solution, it might not be needed..

  11. #11
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by NeedForExcel View Post
    I have handled that and tested that aswell.. Well it works for me, not sure why it does not at your end .. I can only say after seeing the error.. Anyways now that you have a solution, it might not be needed..
    Strange one. Thanks for your help anyway

  12. #12
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,396

    Re: Extracting the first word form every cell in a column

    This macro is similar to the one jindon posted and produces the same calculated output but the resulting formulas in the cells are a little more compact...
    Please Login or Register  to view this content.
    I have a question, though... in your original post, you converted the formulas you placed in your cells with their calculated values. I left the formulas as is because jindon did so in his post and you replied to him that his code worked perfectly. However, if you want values instead of formulas, you can use this macro instead...
    Please Login or Register  to view this content.

  13. #13
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,396

    Re: Extracting the first word form every cell in a column

    Quote Originally Posted by NeedForExcel View Post
    Hi,

    Give this a try

    Please Login or Register  to view this content.
    If you change what I highlighted in red above to this (what I added is shown in green)...

    Split(R(Counter, 1), " ", 2)

    then you can change what I highlighted in blue above to this...

    Temp(1)

    Note: Your code worked for me (as long as there were no blanks cells) with one exception... it parses the header row (I assume the OP has headers since he started his code at cell B2).
    Last edited by Rick Rothstein; 01-29-2020 at 12:26 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help extracting word from a cell please
    By spa3212 in forum Excel General
    Replies: 4
    Last Post: 09-20-2018, 03:48 AM
  2. [SOLVED] Extracting a word from a cell!
    By virsilens in forum Excel General
    Replies: 14
    Last Post: 02-22-2018, 09:46 PM
  3. Extracting Form Fields from Word to Excel run-time error
    By MatthewRyanStiles in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2016, 12:58 PM
  4. [SOLVED] Extracting a word from a cell
    By Fletch74 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-25-2013, 01:47 AM
  5. Extracting a column from form 1 and placing it in form 2
    By vonneffdobermans in forum Excel General
    Replies: 9
    Last Post: 08-02-2009, 09:17 PM
  6. Extracting word from phrase within column
    By KH_GS in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 04-03-2006, 09:50 PM
  7. Replies: 1
    Last Post: 02-25-2005, 06:27 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1