## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

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

Posted on 2013-01-26
227 Views
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
Question by:SteveL13

LVL 43

Assisted Solution

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

LVL 92

Accepted Solution

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

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.