keep only the first word in a cell

[darkhorse4321]

I want to keep only the first word in a cell and remove all the other words in the cell

For e.g. if a cell contains the word “R160 2.15k 7 7 f”,
I want to remove “2.15k 7 7 f” and only keep R160

I want to do this for a entire column A.

Some picture illustration

1) how the sheet looks like initially,
1.JPG

2) after running the macro, the sheet should looks like,
2.JPG


I have attached a sample workbook
Sheet 1 contains a sample data
Sheet2 shows how the data looks like after running the macro
Sheet 3 contains a button to insert the codes
removeWords.xlsx

[sweep]

Hi,

Try this code

Please Login or Register  to view this content.

[Marcol]

Try this

=TRIM(LEFT(A2,FIND(" ",A2)))

Hope this helps

[snb]

or

Please Login or Register  to view this content.

[romperstomper]

No need for a macro:
Select the data, press Ctrl+H, enter a space followed by an asterisk in the Find box, leave the Replace box blank, and press Replace All.

[darkhorse4321]

Thanks guys for the help, all the above codes worked
romperstomper, thanks for providing an alternative to solve this problem.It works too

[snb]

Sure, you won't need a macro with:

Please Login or Register  to view this content.

[romperstomper]

Surely that is a macro by any normal definition?

[darkhorse4321]

Hi everyone,

When I tried all the above codes and methods with my sample worksheet I attached in the beginning of my thread, it worked

But now when I tried the same codes and methods with my actual data, none of the above codes seem to work.

I have attached my actual data below.
These are data that were extracted from an external file
remove.xls

[jolivanes]

You have to get rid of the leading spaces first.

In cell B1:
=TRIM(A1)

In cell C1 put Marcol's formula: =TRIM(LEFT(B1,FIND(" ",B1)))

Pull formulas down to the end of your used range in column A.

Someone will combine these for you or come up with a code to use.

[Marcol]

You will need to use this in B2

=LEFT(TRIM(A2),FIND(" ",TRIM(A2)))

Or using sweeps macro

Please Login or Register  to view this content.

I get the impression from your sample you might actually want to use Text to Columns

You should use the Forum facilities to upload a file, not some external source.

From Forum FAQs

How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

[snb]

Surely that is a macro by any normal definition?

I couldn't agree more.....

[snb]

This will suffice:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[darkhorse4321]

Thanks jolivanes for trying to help.

Thanks Marcol, but your codes did not work for the latest file i uploaded in my previous reply. It cleared the entire cell contents

Thanks snb the codes worked. i used the following codes of yours

[A2:A350] = [if(A1:A350<>"",left(trim(A2:A350),find(" ",trim(A2:A350))),"")]

the code will stop removing the cell content once it reach cell A350. But is it possible for the codes to do until the end of column A that is filled with content? Because i have few other files to do the same task and they all have contents until different rows in column A.

[Marcol]

There is no reason why
In B2
=LEFT(TRIM(A2),FIND(" ",TRIM(A2)))

should not work considering this does

Please Login or Register  to view this content.

This is basically the same solution applied directly using VBa instead of a helper column.

My personal preference is not to destroy source data until all is thoroughly tested, always keep a copy of sheets when using VBa solutions, they can't be undone.

The formula works for me and can also be applied through VBa with

Please Login or Register  to view this content.

This allows for variable list lengths.

[EDIT]

I only copied sweeps' code and added a line, I should have posted

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[darkhorse4321]

Thanks Marcol for your help, your codes below the edit column worked.

Thanks snb your codes also worked.