Solved

Function to remove all character in a cell following the first space

Posted on 2013-01-26
3
228 Views
Last Modified: 2013-01-27
How can I remove all characters in a cell following the first space and remove the space also?

In other words...

"435678TD45   sgepr"

would result in...

"435678TD45" only.

--Steve
0
Comment
Question by:SteveL13
3 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 38822470
=left(A1,find(" ",A1)-1)
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 38822604
In the event you have any entries with no spaces, or blanks, that formula will return an error.

To return the whole string if there are no spaces:

=LEFT(A1,FIND(" ",A1&" ")-1)

This will return a zero-length string if A1 is blank.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question