Excel: Extract characters in cell before the first whitespace IF the cell has whitespace

user_7978062 0
Viewed 1439

I'm using this formula:

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

to extract characters of a cell before the first whitespace. but some cells don't have any whitespace.otherwise just extract all characters int he cell. for instance i have 

apple
banana (yellow)
zucchini (green)

i want the formula to return:

apple
banana
zucchini

but instead i get a VALUE error because because there's no whitespace in the 'apple' cell:

#VALUE!
banana
zucchini
character extract whitespace excel
5 Answers

Using IFERROR and removing the optional start position argument (since 1 is the default):

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

This should work for you

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

user_102711850

I tend to add a dummy space:

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

(doesn't give a zero with empty cells)

user_38949170

I find REPLACE offers more versatility than LEFT. Appending the search character to the original to avoid errors is good practice.

=REPLACE(A2, FIND(" ", A2&" "), LEN(A2), TEXT(,))
Admin1

Just for S&G an user defined function with regex

Option Explicit
Public Sub TEST()
    Dim tests(), i As Long
    tests = Array("apple", "banana (yellow)", "zucchini (green)")

    For i = LBound(tests) To UBound(tests)
        Debug.Print GetString(tests(i))
    Next
End Sub

Public Function GetString(ByVal inputString As String) As String
  With CreateObject("VBScript.RegExp")
            .Global = True
            .MultiLine = True
            .Pattern = "^\S*"
            If .TEST(inputString) Then
              GetString = .Execute(inputString)(0)
            Else
              GetString = vbNullString
            End If
  End With
End Function

Regex:

Try it here

^ asserts position at start of a line

\S* matches any non-whitespace character (equal to [^\r\n\t\f\v ])

* Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy)

Sheet:

data

user_62412350
Related