Extracting the first word form every cell in a column

**mikeymeteora** · 01-28-2020, 08:12 AM

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.

**NeedForExcel** · 01-28-2020, 08:18 AM

Hi,

Give this a try

Please Login or Register  to view this content.

**mikeymeteora** · 01-28-2020, 08:25 AM

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

**jindon** · 01-28-2020, 08:31 AM

Try

Please Login or Register  to view this content.

**AlphaFrog** · 01-28-2020, 08:37 AM

Yet one more...

Please Login or Register  to view this content.

**NeedForExcel** · 01-28-2020, 08:38 AM

Originally Posted by mikeymeteora

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?

**mikeymeteora** · 01-28-2020, 08:39 AM

Originally Posted by jindon

Try

Please Login or Register  to view this content.

Thank you, this worked perfectly

**mikeymeteora** · 01-28-2020, 08:43 AM

Originally Posted by AlphaFrog

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.

**mikeymeteora** · 01-28-2020, 08:46 AM

Originally Posted by NeedForExcel

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.

**NeedForExcel** · 01-28-2020, 08:48 AM

Originally Posted by mikeymeteora

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..

**mikeymeteora** · 01-28-2020, 08:51 AM

Originally Posted by NeedForExcel

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

**Rick Rothstein** · 01-28-2020, 07:21 PM

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.

**Rick Rothstein** · 01-29-2020, 12:23 AM

Originally Posted by NeedForExcel

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).

Extracting the first word form every cell in a column

LinkBack

Thread Tools

Rate This Thread

Display

Extracting the first word form every cell in a column

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Help extracting word from a cell please

[SOLVED] Extracting a word from a cell!

Extracting Form Fields from Word to Excel run-time error

[SOLVED] Extracting a word from a cell

Extracting a column from form 1 and placing it in form 2

Extracting word from phrase within column

After extracting first word from column, how do you delete what you've extracted for

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions