CODEWITHSUNDEEP
vbaexcelexcel-2007
dzilla
dzilla

Reputation: 832

How do I delete all characters after the first space in a cell?

I have a list of city names followed by the state in which they are located all in one column in Excel. How can I delete everything after the first space so that the city name is the only thing that's left in the cell?

example: A1 = "johnson TX"
should be just A1= "johnson"

Upvotes: 3

Views: 34096

Answers (4)

T.M.
T.M.

Reputation: 9948

Use of Split function

An elegant approach is to use the first token of the Split function:

Code Example extracting from cell A1 to A2

Option Explicit

Sub KeepCity()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet")   ' << Change to your sheet name
    ws.[A2] = Split(ws.[A1], " ")(0)
End Sub

Alternative Syntax

Instead of cell abbreviations [A2] or [A1] you can also use:

ws.Range("A2").Value = Split(ws.Range("A1").Value, " ")(0)

Note

The resulting split 1-dimensional array is zero based, so you get the first part (or token) of the original string via index (0).

If you are looking for a second part, I recommend adding an additional delimiter value (" ") to the original string, e.g. s: MsgBox split(s & " "," ")(1). By that way you avoid error number 9 "Subscript out of range", if there is no delimiter in between at all, thus no second part at all.

Upvotes: 1

Jean-Fran&#231;ois Corbett
Jean-Fran&#231;ois Corbett

Reputation: 38551

I assume you want a VBA solution since you tagged your question .

This works:

Sub KeepCity()
    Dim strCityAndState As String
    Dim strCityOnly As String
    strCityAndState = Range("A1").Value
    strCityOnly = Left(strCityAndState, InStr(strCityAndState, " ") - 1)
    Range("A2").Value = strCityOnly
End Sub

If you don't want VBA and want a cell formula instead, then @JToland's answer works fine, though this one is more concise and doesn't keep the trailing space character:

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

Upvotes: 12

Pavan Chinta
Pavan Chinta

Reputation: 1

If you are looking for a VBA function, you can use Left and InStr as shown below.

Dim Temp As String: Temp = "Hello_World! This is my first answer here. :D"
Temp = Left(Temp, InStr(Temp, " ")-1)

In which case, Temp will be "Hello_World!"

Upvotes: 0

JToland
JToland

Reputation: 3710

Well doing something like this

=Mid(A1, 1, Find(" ",A1))

in another column should grab all text before the " ". So you could build another column with just all the city names and then you could use that column for your purposes.

Upvotes: 6

Related Questions